How To Insert Multiple Values In Multiple Table Using Stored Procedure

In this blog, you will learn how to insert multiple values in the multiple tables, using stored procedure. You have inserted multiple values in the multiple tables one by one in SQL Server. Today, you will learn how to insert multiple values in multiple tables, using stored procedure. I have shared below queries and screenshots. You can follow them and you will easily understand how to insert multiple values in the multiple tables as well.

 

First, you make table  in SQL Server. I have made a table and named it as Empl. You can see SQL query, given below-

create table Empl(E_ID int primary key identity(1,1), Name varchar(50), Designation varchar(50))

I have made the second table and named it as Location and made relationship with Empl table. You can see SQL query, given below-

create table Location(L_ID int primary key identity(1,1), Location varchar(50), E_ID int foreign key references Empl(E_ID))

Now, I have made the third table and named it as Contact Emp and made relationship with Location table. You can see SQL query, given below-

create table Contact_Emp (C_ID int primary key identity(1,1), Contact_Number varchar(50), Lo_ID int foreign key references Location(L_ID))

These are three tables. Now, you will make a stored procedure and pass the parameters.Use @@identity, which is used to get the last inserted value, given below. You can see SQL query,  given below-

create proc InsertMultiplevalue
(
@Name varchar(50),
@Designation varchar(50),
@Location varchar(50),
@Contact varchar(50)
)
as
begin
        insert into Empl values(@Name, @Designation)
        declare @Employee_ID int = @@identity
        insert into Location values(@Location,@Employee_ID)
        declare @Cot_ID int = @@identity
        insert into Contact_Emp values(@Contact,@Cot_ID)
end

Finally, simply execute stored procedures and insert values in a single SQL query, given below-

exec InsertMultiplevalue 'Asif Ali','Programmer','Hyderabad','[email protected]'