SQL Server: How to Insert into Two Tables At the Same Time

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.

SQL Server : insert into two tables with case

You'll need to do a couple of things to make this work. For the first part of your stored procedure you'll need to insert into the tblProduct table, then store the new ProductCode in a variable. I'm assuming that ProductCode is generated via trigger or computed column. If you're not automatically generating that, the best way to do that is by creating a computed column.

Below you create your procedure with the ProductName and ProductPrice as parameters, insert them into your tblProduct, and store the new ProductCode into a @NewProductCode temp table.

CREATE PROCEDURE dbo.InsertProduct @ProductName nvarchar(255), @ProductPrice decimal(19,4)
AS
    DECLARE @NewProductCodeTempTable table (ID int)

    INSERT INTO tblProduct (ProductName, ProductPrize) 
    OUTPUT INTO INSERTED.ProductCode @NewProductCode
    VALUES (@ProductName, @ProductPrice)

Next you'll use an IF/ELSE statement to determine whether you need to insert one, or 4 records into your tblPriceMST table.

    DECLARE @NewProductCode = SELECT ID FROM @NewProductCodeTempTable

    IF (@ProductPrice < 300)   
        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-01', @ProductPrice, 1, 'KG')
    ELSE 
    BEGIN
        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-01', @ProductPrice, 1, 'KG')

        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-02', @ProductPrice*.75, 750, 'GMS')   

        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-03', @ProductPrice*.5, 500, 'GMS')  

        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-04', @ProductPrice*.25, 250, 'GMS')            
    END
GO

I've inferred what your rules are for inserting records for the different product weights if the price is > 300. But if you have more rules, you would add additional IF/THEN or CASE statements in the ELSE block here.

There may be a much easier way to do this if you simplify your table design but without knowing all of your requirements it would be hard to say what it should look like.

Insert and update two tables at the same time

The best approach here is to preserve ids for later mapping.You can use a temporary table for that.

CREATE TABLE #MigrationMapper
(   OldId INT,
    NewId INT )

Then you can populate this table while inserting the data into the new table. You can make use of OUTPUT clause to get Inserted Id's

MERGE INTO Account AS T 
USING AdventureWorks_Test.dbo.Customer s
ON t.Id=a.Id+ NULL --To insert all records 
WHEN NOT MATCHED THEN 
INSERT ( Email)
VALUES ( s.EmailAddress)
OUTPUT s.Id, INSERTED.ID INTO #MigrationMapper(OldId,NewId); 

So by joining with the temp table now, you know which email address goes with the new customer row

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]
;

SQL: Is it possible to insert into two tables at the same time with autogenerated id output from first table into second

As I mention, you need to use the OUTPUT clause. This is pseudo-SQL in the abscence of consumable sample data and expected results. You'll need to remove/change the parts in braces ({}) appropriately:

DECLARE @Output table (ID uniqueindentifier,
                       {Other Columns});

INSERT INTO FileStore(FileName,Description)
OUTPUT INSERTED.Id, {Other Columns}
INTO @Output
SELECT FileName,
       Description
from FileStore
where Id IN ('b3304dc4-4f2e-46d4-8f64-a597edb02e96','7cb40baf-1ecf-4e5f-92ff-57029a20e623');

INSERT INTO FileRepository(Name,FileId)
SELECT FR.Name,
       O.ID
from FileRepository FR
     {CROSS} JOIN @Output O {ON Some Condition}
where FileId IN ('b3304dc4-4f2e-46d4-8f64-a597edb02e96','7cb40baf-1ecf-4e5f-92ff-57029a20e623')

Inserting data into multiple tables at same time

When you run your second query, it also matches the just inserted rows. You should do something like this:

Insert INTO dbo.Buffer
Select src.ID, src.name, 'flagB'
FROM dbo.Source src
    inner join dbo.Destination dest on src.Name = dest.Name
where not exists (
    select * from dbo.Buffer b where b.xxx = 'flagA' and b.yyy = src.name
)

Or just use when matched by target, as LONG suggested.

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

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

---

Related Topics