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

User · Accepted Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

--                                                  -- Template generated from Template Explorer using  -- Create Procedure  New Menu  SQL -- -- Use the Specify Values for Template Parameters  -- command  Ctrl-Shift-M  to fill in the parameter  -- values below  -- -- This block of comments will not be included in -- the definition of the procedure  --                                                  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO  ALTER PROCEDURE InsetIntoTwoTable     name nvarchar 50    Email nvarchar 50     AS BEGIN      SET NOCOUNT ON        insert into dbo info name  values   name      insert into dbo login Email  values   Email  END GO

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

--                                                  -- Template generated from Template Explorer using  -- Create Procedure  New Menu  SQL -- -- Use the Specify Values for Template Parameters  -- command  Ctrl-Shift-M  to fill in the parameter  -- values below  -- -- This block of comments will not be included in -- the definition of the procedure  --                                                  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO  ALTER PROCEDURE InsetIntoTwoTable     name nvarchar 50    Email nvarchar 50     AS BEGIN      SET NOCOUNT ON        insert into dbo info name  values   name      insert into dbo login Email  values   Email  END GO

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to insert

Examples related to temp-tables