SQL Insert into Joined Tables

[cjennings]

I need to insert data into 2 linked tables which have unique identifiers created automatically (set as an identity in sql server). Do i need to execute multiple statements in order to do the insert or can it be done with just one. If i do it with multiple statements how do i return the unique id from table 1 in order to insert into table 2.

Thanks
Cameron

[Iguana]

Yes you need 2 separate statements

The easiest way to do this is to use a stored procedure and use the @@IDENTITY system variable that picks up the value of last Identity generated

INSERT INTO tbl1(datafield) VALUES(@datafield1)

Set @tbl1ID = @@IDENTITY

INSERT INTO tbl2(tbl1id, datafield) VALUES(@tbl1ID, @datafield2)

There is a way to pick up the identity field on the client side but using stored procedures is good practice

[cjennings]

Thanks Iguana - Had just worked it out for myself as you posted the reply. Only took me 6hrs to find the @@IDENTITY funtion. I new it would be simple!