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:
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?
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