CODEWITHSUNDEEP
sqlsql-servert-sql
moe
moe

Reputation: 5249

Insert into 2 tables from a single select query using TSQL

I am trying to insert into 3 tables from one single select statement. Here is what I am trying to do:

insert into dbo.temp1 (name, location, city)
select name, location, city from mytable.

I want to be able to insert into 3 tables once I run the select statement like inserting into temp1, temp2 and temp3.

How can I do this? Thanks.

Upvotes: 1

Views: 3836

Answers (7)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

You can do it maximum for 2 tables with using output:

insert into dbo.temp1 (name, location, city)
output inserted.name, inserted.location, inserted.city into temp2
select name, location, city from mytable

Upvotes: 3

Suing
Suing

Reputation: 453

MySQL doesn't support multi-table insertion in a single INSERT statement. Oracle is the only one I'm aware of that does, oddly...

However, you CAN use a transaction and have both of them be contained within one transaction.

MySQL:

START TRANSACTION;
  INSERT INTO table1 VALUES ('1','2','3');
  INSERT INTO table2 VALUES ('1','2','3');
COMMIT;

SQL Server:

BEGIN TRAN;
  INSERT INTO table1 VALUES ('1','2','3');
  INSERT INTO table2 VALUES ('1','2','3');
COMMIT;

SQL Server with error catching/rollback:

BEGIN TRANSACTION [Tran1]

  BEGIN TRY

    INSERT INTO table1 VALUES ('1','2','3')    
    INSERT INTO table2 VALUES ('1','2','3')  


    COMMIT TRANSACTION [Tran1]

  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION [Tran1]
  END CATCH  

GO

Upvotes: 0

alzaimar
alzaimar

Reputation: 4622

You can. With a trick.

Create a view, then create an 'instead of' trigger for insert on that view where you insert the stuff into your tables. If you now insert into your view, you finally insert data in 3 tables. Here's a demo

-- 1. create 3 test tables
create table t1( id int, f1 varchar(20))
create table t2( id int, f2 varchar(20))
create table t3( id int, f3 varchar(20))
go

-- 2. create the view
create view Tt as
  select t1.ID, t1.f1, t2.f2,t3.f3
    from t1 
    join t2 on t1.ID=t2.ID 
    join t3 on t1.ID=t3.id
go

-- 3. create the trigger
create trigger Tr_Test on Tt INSTEAD OF INSERT
AS 
BEGIN
  SET NOCOUNT ON;
  insert into t1 select id,f1 from inserted
  insert into t2 select id,f2 from inserted
  insert into t3 select id,f3 from inserted
END
GO

-- 4. now do your insert with a single select 
insert into tt 
  select 1,'A','B','C'

-- 5. and watch the 3 tables
select * from t1
select * from t2
select * from t3

voilá, one insert, 3 tables got modified. Wwe don't count the hidden trigger, do we ;-)

Upvotes: 1

Mackers
Mackers

Reputation: 1050

You can insert into multiple tables with one select statement using a TRIGGER.

CREATE TRIGGER TEMP2_TEMP3_INSERT ON TEMP1
  AFTER INSERT AS
BEGIN
  /* create your insert statements for TEMP2 and TEMP3 here
  referencing the data from the first insert */
END;
GO

Upvotes: 0

SQL Taylor
SQL Taylor

Reputation: 138

I don't believe you can insert into multiple tables in one statement. You can definitely do it in one transaction, however. 

BEGIN TRANSACTION
    INSERT INTO dbo.temp1 (name, location, city)
    SELECT name, location, city 
      FROM myTable
    INSERT INTO dbo.temp2 (name, location, city)
    SELECT name, location, city
      FROM myTable2
COMMIT TRANSACTION

Upvotes: 0

StuartLC
StuartLC

Reputation: 107407

You can't do this in one step*

What you can do is to insert the initial query into a #temp table (or a @table variable) as a staging area, and then insert into the tables from there. Wrap the steps in a transaction to retain ACID:

   BEGIN TRAN

   select name, location, city 
       into #TEMP
       from mytable;

   insert into temp1(name, location, city)
     select name, location, city 
     from #TEMP;

    -- Same for temp2 and temp3.

   COMMIT TRAN

* Excluding hacks such as a view with an Instead-of Trigger.

The staging table is important from a concurrency point of view, as repeating the original query 3 times may result in different results if there are interim concurrent changes to the source table.

Upvotes: 2

VoonArt
VoonArt

Reputation: 904

There is no way to insert into X tables with one query (Ok it its with insert and output to table).

So you have to write 3 queries.

Or you can generate SQL statments with dynamic queries.

Upvotes: 0

Related Questions