How to Insert Data into Two Tables Simultaneously in SQL Server

How can I INSERT data into two tables simultaneously in SQL Server?

Try this:

insert into [table] ([data])
output inserted.id, inserted.data into table2
select [data] from [external_table]

UPDATE: Re:

Denis - this seems very close to what I want to do, but perhaps you could fix the following SQL statement for me? Basically the [data] in [table1] and the [data] in [table2] represent two different/distinct columns from [external_table]. The statement you posted above only works when you want the [data] columns to be the same.

INSERT INTO [table1] ([data]) 
OUTPUT [inserted].[id], [external_table].[col2] 
INTO [table2] SELECT [col1] 
FROM [external_table] 

It's impossible to output external columns in an insert statement, so I think you could do something like this

merge into [table1] as t
using [external_table] as s
on 1=0 --modify this predicate as necessary
when not matched then insert (data)
values (s.[col1])
output inserted.id, s.[col2] into [table2]
;

How can I insert multiple row data into two tables simultaneously

You can use output clause to manage multiple inserted rows. Something like this.

DECLARE @ids table (DataID int);
BEGIN TRANSACTION
INSERT INTO [TableB] ([Data1]) 
output inserted.id into @ids --collect identity id's
SELECT  NEWID() FROM TableA WHERE [DeptCode] = 101;

INSERT INTO Table3 
select 'some data', 'some more data', DataID
from @ids;
COMMIT

SQL INSERT INTO from multiple tables

You only need one INSERT:

INSERT INTO table4 ( name, age, sex, city, id, number, nationality)
SELECT name, age, sex, city, p.id, number, n.nationality
FROM table1 p
INNER JOIN table2 c ON c.Id = p.Id
INNER JOIN table3 n ON p.Id = n.Id

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

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.

Insert Stored procedure to insert data into two tables at a time in SQL Server 2008

You can use scope_identity() to catch the id being inserted into dbo.Login.
Like this:

drop table if exists dbo.UserInfo;
drop table if exists dbo.Login;


create table dbo.Login (
    id int not null identity (1, 1) primary key
    , username varchar(100)
    , password varchar(100)
);

create table dbo.UserInfo (
    id int not null primary key foreign key references dbo.Login(id)
    , first_name varchar(100)
    , last_name varchar(100)
    , phone_no varchar(100)
    , email varchar(100)
    , address varchar(100)
    , IsActive bit
);
go


create or alter procedure dbo.InsertIntoLoginAndUserInfo
(
    @username varchar(100)
    , @password varchar(100)
    , @first_name varchar(100)
    , @last_name varchar(100)
    , @phone_no varchar(100)
    , @email varchar(100)
    , @address varchar(100)
    , @IsActive bit
)
as

begin

    declare @id int;

    insert into dbo.Login (username, password)
    values (@username, @password);
    set @id = scope_identity();

    insert into dbo.UserInfo (id, first_name, last_name, phone_no, email, address, IsActive)
    values (@id, @first_name, @last_name, @phone_no, @email, @address, @IsActive);

end

Insert data into multiple tables from 1 source

Do that in steps. First create your table code with a reference to the existing tables: code(id, description, idx, idy).

Then insert records:

insert into code (idx, idy, description)
select x.id, y.id, x.description + ' - ' + y.description
from x
cross join y;

then create the table intermediate and fill it:

insert into intermediate (idcode, number1, number2, idemployee)
select code.id, z.number1, z.number2, employees.id
from code
join z on z.idx = code.idx and z.idy = code.idy
cross join employees;

Then you can remove idx and idy from table code and drop your old tables.

SQL - How to Insert same value into two tables simultaneously

You are right. For this case transactions were invented. Try this:

START TRANSACTION;
INSERT INTO player
(facebook_id, first_name, last_name, gold_balance) 
VALUES 
('$userid', '$userfirst_name', '$userlast_name', “100”)

INSERT INTO BALANCE
(facebook_id, first_name, last_name, gold_balance) 
VALUES 
('$userid', '$userfirst_name', '$userlast_name', “100”)"

commit;       

Fix insert statements to fit tables.

But i see no problems to use two separate queries like you were already suggested.

---

Related Topics