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


Step1:

Create first table: 


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

Step2:

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
)

Step3:

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
as
begin
     insert into Registraion(name)values(@name)
     declare @id int
     select @id = scope_identity()
     insert into Registraion_Exp(regid,current_company,
                        current_salary,expected_salary,
                                    notice_period)                                            values(@id,@current_company,
                      @current_salary,@expected_salary,
                                    @notice_period)         
end



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







Previous
Next Post »