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

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?

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.

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.

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

[To see links please register here]

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.

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.

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?

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][1] 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


[1]:

[To see links please register here]

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.

//if you want to insert the same as first table

$qry = "INSERT INTO table (one, two, three) VALUES('$one','$two','$three')";

$result = @mysql_query($qry);

$qry2 = "INSERT INTO table2 (one,two, three) VVALUES('$one','$two','$three')";

$result = @mysql_query($qry2);


----------


//or if you want to insert certain parts of table one


$qry = "INSERT INTO table (one, two, three) VALUES('$one','$two','$three')";


$result = @mysql_query($qry);

$qry2 = "INSERT INTO table2 (two) VALUES('$two')";

$result = @mysql_query($qry2);

//i know it looks too good to be right, but it works and you can keep adding query's just change the

"$qry"-number and number in @mysql_query($qry"")


----------


I have 17 tables this has worked in.

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.