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

Question

My database contains three tables called Object_Table, Data_Table and Link_Table. The link table just contains two columns, the identity of an object record and an identity of a data record.

I want to copy the data from DATA_TABLE where it is linked to one given object identity and insert corresponding records into Data_Table and Link_Table for a different given object identity.

I can do this by selecting into a table variable and the looping through doing two inserts for each iteration.

Is this the best way to do it?

Edit : I want to avoid a loop for two reason, the first is that I'm lazy and a loop/temp table requires more code, more code means more places to make a mistake and the second reason is a concern about performance.

I can copy all the data in one insert but how do get the link table to link to the new data records where each record has a new id?

Answer 1

If you want the actions to be more or less atomic, I would make sure to wrap them in a transaction. That way you can be sure both happened or both didn't happen as needed.

Answer 2

Insert can only operate on one table at a time. Multiple Inserts have to have multiple statements.

I don't know that you need to do the looping through a table variable - can't you just use a mass insert into one table, then the mass insert into the other?

By the way - I am guessing you mean copy the data from Object_Table; otherwise the question does not make sense.

Answer 3

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

Answer 4

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

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.

Answer 5

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.

Answer 6

You might create a View selecting the column names required by your insert statement, add an INSTEAD OF INSERT Trigger, and insert into this view.

Answer 7

Before being able to do a multitable insert in Oracle, you could use a trick involving an insert into a view that had an INSTEAD OF trigger defined on it to perform the inserts. Can this be done in SQL Server?

Answer 8

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.