MSSQL Insert into multiple tables
Posted on 2010-08-16
I want to insert into 2 tables multiple values. e.g.
I have 2 tables.
Table 1 = Emails_Sent
ES_ID, Cont_ID, DateTime
Table2 = Countrys_SentTo
CST_ID, ES_ID, Country_ID
A contact has multiple Countries. When an e-mail is sent to them it logs in the Emails_Sent
and also logs the countries that contact is responsible for.
If this was just one I'd have no problem using SCOPE_IDENTITY(), but I need to be able to do multiple e-mails sent at once
I have tried
Declare @ID int
Insert into Emails_Sent (Cont_ID, DateTime) Select Cont_ID, getdate() From MyContTable
Set @ID = SCOPE_IDENTITY()
Insert into Countrys_SentTo (ES_ID, Country_ID) Select @ID, County_F From MyCont_County_Table
The other issue I have is linking the 2 inserts into together e.g.MyContTable.Cont_ID = MyCont_County_Table.Cont_ID
I think I need to loop through the first, but don’t know how to do this.
Can any one help