Insert to two related tables

[zatch]

I need to run a query which inserts to two related tables where the parent table has an identity field.

Table A - Employer
Employer_Id (Identity Field)
First_Name
Last_Name

Table B - Employee
Employer_Id (Foreign Key)
First_Name
Last_Name

I have no problem selecting and inserting the data into Table A, the problem I'm having is that I don't know how to then grab the newly created Employer_Id from this table so that I can use it to insert the record(s) into Table B.

Any help is greatly appreciated.

Zatch

 

[JayKusch]

Since it looks as if this is an employee table, I would guess there would not be "too" many inserts going on.

What you could do is use scope_identity() to pull the new ID and use it in the insert into table 2 OR you could create an INSERT Trigger on table1

Thanks

J. Kusch