brasso_42
asked on
MSSQL Insert into multiple tables
Hi
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_I D
I think I need to loop through the first, but don’t know how to do this.
Can any one help
Thanks
Brasso
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_I
I think I need to loop through the first, but don’t know how to do this.
Can any one help
Thanks
Brasso
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awsome workd like a dream!
Many thanks
Brasso
Many thanks
Brasso
Open in new window