• 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.