Brandon Terry Brandon Terry - 4 months ago 10
SQL Question

SQL SERVER How to insert to 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 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