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