How to Insert into Two Tables All at Once in a Stored Procedure

SQL Server stored procedure to insert in multiple tables

It will be something like below. You can use SCOPE_IDENTITY() to get the last autogenerated ID withing the scope which is this stored proc in this case:

create procedure NameOfYourProcedureHere
as
begin
SET NOCOUNT ON;
SET XACT_ABORT ON;

    insert into custlogin(custusename, custpassword) 
        values ('','') -- put values here (from parameters?)

    insert into custinfo(custid, custfirstname, custlastname, custaddress)
        values (SCOPE_IDENTITY(), '', '', '')  -- put other values here (from parameters?)

SET NOCOUNT OFF;
SET XACT_ABORT OFF;
end

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

How do I insert into two tables all at once in a stored procedure?

You need to do something like this:

DECLARE @NewID INT

INSERT INTO Customer(LastName,FirstName,......) VALUES(Value1, Value2, .....)

SELECT @NewID = SCOPE_IDENTITY()

INSERT INTO Employment(CID,Employer,.....) VALUES(@NewID, ValueA,..........)

SCOPE_IDENTITY: Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

Insert with stored procedure into multiple tables with foreign keys

Here is code which corrects the issues you face.

1. You are guessing what the new address record ID will be and are inserting it into the user table before it exists. This is very bad practice, not only could it fail at some point, but you aren't using a foreign key to enforce the relationship - meaning you could end up with bad data in your database over time.
2. The easiest way to get a single value back from a stored procedure is to use an OUT parameter.
3. Following the referential integrity rules, you need to insert the address record first, so that it exists (this would be enforced if you had a foreign key), prior to inserting the user.

Full code below.

CREATE PROCEDURE insert_user
(
    @name VARCHAR(20)
    , @sex CHAR(1)
    , @date_of_birth DATE
    , @account_type INT
    , @address_id INT
    , @user_id INT OUT
)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [user]([user_name],[User_Sex], [date_of_birth], [account_type], [id_address])
    VALUES(@name, @sex, @date_of_birth, @account_type, @id_address);

    -- If we inserted a row, get the new ID
    if @@ROWCOUNT = 1 set @user_id = SCOPE_IDENTITY();

    RETURN 0;
END
go

CREATE PROCEDURE insert_address
(
    @street VARCHAR(255)
    , @number INT
    , @locality VARCHAR(255)
    , @city VARCHAR(255)
    , @country_code CHAR(2)
    , @address_id INT OUT
)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [address]([street], [number], [locality], [city], [country_code])
    VALUES(@street, @number, @locality, @city, @country_code);

    -- If we inserted a row, get the new ID
    if @@ROWCOUNT = 1 set @address_id = SCOPE_IDENTITY();

    RETURN 0;
END
go

CREATE PROCEDURE insert_new_user
(
    @name VARCHAR(20)
    , @sex CHAR(1)
    , @date_of_birth DATE
    , @account_type INT
    , @street VARCHAR(255)
    , @number INT
    , @locality VARCHAR(255)
    , @city VARCHAR(255)
    , @country_code CHAR(2)
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @address_id INT, @user_id INT;

    EXEC insert_address @street, @number, @locality, @city, @country_code, @address_id OUT;

    IF @address_id IS NOT NULL BEGIN
        EXEC insert_user @name, @sex, @date_of_birth, @account_type, @address_id, @user_id OUT;
        -- Not sure what this does as @style is not defined anywhere
        -- But if it needs a user_id - we have one 
        -- EXEC insert_style @style;
    END;

    RETURN 0;
END
GO

EXEC insert_new_user 'tatiana', 'f', '1970-02-07', 1, 'rue steyls', 13, 'laeken', 'bruxelles', 'be';

Can I use one stored procedure to insert values into two tables

yup, easily:

CREATE PROCEDURE [dbo].[InserIntoTwoTables]
    @arg1 INT, 
    @arg2 INT,
    @arg3 INT, 
    @arg4 INT
AS
BEGIN

    INSERT INTO Table1 (col1 ,col2)
    VALUES (@arg1 , @arg2) 

    INSERT INTO Table2 (col3 ,col4)
    VALUES (@arg3 , @arg4) 

END
GO

Frankly, it does not get anymore straightforward than that...

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.

SQL Server stored procedure with multiple table insert

You are using IDENTITY_INSERT and inserting your own values for PhoneId and AddressId. SCOPE_IDENTITY() is always the last value inserted into an identity column, so after you insert into phone it will return PhoneId and never AddId.

Option 1: Use the values you have. This is a bad idea because if you do two adds at the same time, you might get conflicts

SET IDENTITY_INSERT dbo.Customers ON
INSERT INTO Customers (CustomerID,AddressID,PhoneID,FirstName,LastName)
VALUES (@CustID, @AddId, @PhoneId ,@FirstName, @LastName)
SET IDENTITY_INSERT dbo.Customers OFF

Option 2: Just let the system set the identity values and get them after each insert, i.e.:

INSERT INTO Addresses (Street, City, States, Zip)
VALUES (@Street,@City,@State,@Zip)
SET @AddId = SCOPE_IDENTITY()

---

Related Topics