Stored procedure to insert data into multiple tables in sql server

Inserting into multiple tables:

Introduction: In this article I am going to explain how to insert data into multiple tables . For this you have to basic knowledge of store procedure. Let's get start step by step.

insert data into multiple tables in sql server


Create first table: 

create table Registraion
regid int primary key identity,
name varchar(50)


Create second table: 

create table Registraion_Exp
regexpid int primary key identity,
regid int foreign key references Registraion(regid),
current_company varchar(50),
current_salary int,
expected_salary int,
notice_period int


Now, I am creating a store procedure to insert data in two tables at a time in mssql , this is very simple.

Create proc usp_registration_insert
@name varchar(50),
@current_company varchar(50),
@current_salary int,
@expected_salary int,
@notice_period int
     insert into Registraion(name)values(@name)
     declare @id int
     select @id = scope_identity()
     insert into Registraion_Exp(regid,current_company,
                                    notice_period)                                            values(@id,@current_company,

Select @id = scope_identity(): (Use of scope_identity)
Used for selecting the latest value(primary key which is also a foregin key) of table first, if their will no reference between these tables it means that we can not get latest value of first table.

Read More - Know More

Next Post »