🔒 Locked

SQL question

Started by NewDeal Jun 29, 2000 at 5:32 PM 4 replies 650+ views

Original Post

Jun 29, 2000 at 5:32 PM

This probably isnt the right place to ask this question, but here goes. In my SQL server i have 2 table: table1 has 2 columns, id (identity), and firstname (varchar). table2 has 2 columns, id (identity), and lastname (varchar). What i want to do is to insert firstname and lastname, into table1 and table2 in a single SQL call. The catch is that i would like the 2 id columns to be equal as well. Equal so that i can match all firstnames in table1 with 1 lastname in table2. Can all this be done in a single sql call? The database im working with is a lot more complicated than above, but i think it explains my problem. Ive never really used SQL much, so any help would be very appreciated. Thanx alot

habus

Jun 29, 2000 at 6:23 PM

Hi!

Maybe I''m not a master sql programmer but I guess you might want to change a little your tables..Why don''t you just create one table with:
Id
FirstName
LastName

I know this way you waste some space (while repeating the lastname) but you can make all these three fields a key field so your searchs become faster and your inserts easiers, you''d be able to do in in on sentence:

Insert into TableA(Id,FirstName,LastName)
values(1,''MyName'',''MyLastN'')

I can''t think of a way to insert in both tables with just one statement..

Hope this helps!

NewDeal

Jun 29, 2000 at 8:05 PM

Yeah, i know. What u described is the obvious way of doing it. However, i need to do it the other way. The example in my last post was nothing more than an example.

Thanx anyways tho

chewy

Jun 30, 2000 at 10:28 AM

Unfortuately you can not insert into multiple tables with a single sql statement.

Why do you need to do it in 1 statement?

DeltaVee

Jun 30, 2000 at 11:10 AM

1. You cannot update two tables at the same time
2. you cannot ''force'' an identity to a specific value.
3. Without a complete picture of your data model I would suggest that in table one change the identity column to a foreign key (referencing the identity column in Table 2)
4. This will probably start a flame war, but, SQL makes for a sh***y language for First Person shooters and Space Sims ;P .

--------------------------
Carpe Diem

habus

Jun 30, 2000 at 2:04 PM

Hi again!

Question: Does the use of a stored procedure count for you as one sql statement?? You can create one procedure in your server and your application would only need to call it, something like:
begin
pMyProcedure(Id,FirstNam,LastName);
end;

Laters!

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.