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

sql sql-server insert temp-tables
tpower picture tpower · Oct 6, 2008 · Viewed 334.7k times · Source

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

Joel Coehoorn picture Joel Coehoorn · Oct 6, 2008

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.

Related questions

Inserting multiple rows in a single SQL query?

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "…

Read More
Solutions for INSERT OR UPDATE on SQL Server

Assume a table structure of MyTable(KEY, datafield1, datafield2...). Often I want to either update an existing record, or insert a new record if it doesn't exist. Essentially: IF (key exists) run update command ELSE run insert command What's the …

Read More
Exporting data In SQL Server as INSERT INTO

I am using SQL Server 2008 Management Studio and have a table I want to migrate to a different db server. Is there any option to export the data as an insert into SQL script??

Read More