Brandon Terry Brandon Terry - 4 months ago 10
SQL Question

SQL Server : how to insert into 2 different tables

This seems like a really simple answer, but for some reason I can't wrap my head around how I should accomplish this...

I've got two different tables:


  1. Campaign

  2. Campaign_Customer



Where
dbo.Campaign
is all of the details of the campaign, and
dbo.Campaign_Customer
is basically a summary table that contains the
CampaignID
,
CustomerID
,
CreationDate
, and
DeletionDate
(if there is one)

So, when I go to insert a new record into my
dbo.Campaign
table, I need to be able to use the
CampaignID
that it generates to create a record into my
dbo.Campaign_Customer
table.

I know this is possible, but I don't know the proper way to do so. Any help?

Answer

You will need to have 2 separate insert statements within transaction. If your campaign Id is identity column you can get its value using scope_identity function in select after first insert

BEGIN TRANSACTION
INSERT INTO dbo.Campain (x, y, z) VALUES (x, y, z)
DECLARE @id int
SELECT @id = scope_identity()

INSERT INTO dbo.Campaign_Customer (CampaignId, x, y) VALUES (@id, x, y)
COMMIT
Comments