agabb (10/22/2012)
INSERT INTO [ADDRESS] ([CLIENT_NAME], [ADDRESS1])VALUES ('ABC CORP', '50 Langridge St.');
INSERT INTO [PROJECT] ([PROJ_NUMBER], [PROJ_NAME], CLIENT_ADDR_ID])
VALUES ('A445566', 'My Project Name', SELECT SCOPE_IDENTITY());
Slight modification for valid T-SQL:
INSERT INTO [ADDRESS] ([CLIENT_NAME], [ADDRESS1])
VALUES ('ABC CORP', '50 Langridge St.');
INSERT INTO [PROJECT] ([PROJ_NUMBER], [PROJ_NAME], CLIENT_ADDR_ID])
VALUES ('A445566', 'My Project Name', (SELECT SCOPE_IDENTITY()));
or:
INSERT INTO [ADDRESS] ([CLIENT_NAME], [ADDRESS1])
VALUES ('ABC CORP', '50 Langridge St.');
INSERT INTO [PROJECT] ([PROJ_NUMBER], [PROJ_NAME], CLIENT_ADDR_ID])
SELECT 'A445566', 'My Project Name', SCOPE_IDENTITY();
However. you're right to question for multiple inserts - this method only works for a single insert.
As Eugene says, for multiple inserts you can use the OUTPUT clause - grab the output from the Address insert into a table variable and then from there into the Project table. Alternatively you could create a trigger on the Address table that would do the same.