SQL Server: Is it possible to insert into two tables at the same time?

Question

In one statement: No.

In one transaction: Yes

BEGIN TRANSACTION
   DECLARE @DataID int;
   INSERT INTO DataTable (Column1 ...) VALUES (....);
   SELECT @DataID = scope_identity();
   INSERT INTO LinkTable VALUES (@ObjectID, @DataID);
COMMIT

The good news is that the above code is also guaranteed to be atomic, and can be sent to the server from a client application with one sql string in a single function call as if it were one statement. You could also apply a trigger to one table to get the effect of a single insert. However, it's ultimately still two statements and you probably don't want to run the trigger for every insert.

You still need two INSERT statements, but it sounds like you want to get the IDENTITY from the first insert and use it in the second, in which case, you might want to look into OUTPUT or OUTPUT INTO: http://msdn.microsoft.com/en-us/library/ms177564.aspx

The following sets up the situation I had, using table variables.

DECLARE @Object_Table TABLE
(
    Id INT NOT NULL PRIMARY KEY
)

DECLARE @Link_Table TABLE
(
    ObjectId INT NOT NULL,
    DataId INT NOT NULL
)

DECLARE @Data_Table TABLE
(
    Id INT NOT NULL Identity(1,1),
    Data VARCHAR(50) NOT NULL
)

-- create two objects '1' and '2'
INSERT INTO @Object_Table (Id) VALUES (1)
INSERT INTO @Object_Table (Id) VALUES (2)

-- create some data
INSERT INTO @Data_Table (Data) VALUES ('Data One')
INSERT INTO @Data_Table (Data) VALUES ('Data Two')

-- link all data to first object
INSERT INTO @Link_Table (ObjectId, DataId)
SELECT Objects.Id, Data.Id
FROM @Object_Table AS Objects, @Data_Table AS Data
WHERE Objects.Id = 1

Thanks to another answer that pointed me towards the OUTPUT clause I can demonstrate a solution:

-- now I want to copy the data from from object 1 to object 2 without looping
INSERT INTO @Data_Table (Data)
OUTPUT 2, INSERTED.Id INTO @Link_Table (ObjectId, DataId)
SELECT Data.Data
FROM @Data_Table AS Data INNER JOIN @Link_Table AS Link ON Data.Id = Link.DataId
                INNER JOIN @Object_Table AS Objects ON Link.ObjectId = Objects.Id 
WHERE Objects.Id = 1

It turns out however that it is not that simple in real life because of the following error

the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship

I can still OUTPUT INTO a temp table and then finish with normal insert. So I can avoid my loop but I cannot avoid the temp table.

I want to stress on using

SET XACT_ABORT ON;

for the MSSQL transaction with multiple sql statements.

See: https://msdn.microsoft.com/en-us/library/ms188792.aspx They provide a very good example.

So, the final code should look like the following:

SET XACT_ABORT ON;

BEGIN TRANSACTION
   DECLARE @DataID int;
   INSERT INTO DataTable (Column1 ...) VALUES (....);
   SELECT @DataID = scope_identity();
   INSERT INTO LinkTable VALUES (@ObjectID, @DataID);
COMMIT

It sounds like the Link table captures the many:many relationship between the Object table and Data table.

My suggestion is to use a stored procedure to manage the transactions. When you want to insert to the Object or Data table perform your inserts, get the new IDs and insert them to the Link table.

This allows all of your logic to remain encapsulated in one easy to call sproc.