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 using a single query in SQL server. For this, you have to have basic knowledge of store procedures. Let's get started step by step.

1. When we insert data into multiple tables
When we have the requirement to normalize the table data. Normalization is the process of minimizing redundancy from a relation.


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(the primary key which is also a foreign key) of the table first.SCOPE_IDENTITY returns the last identity generated in the current scope. A scope is defined as a module - a stored procedure, function or batch.SCOPE_IDENTITY is limited to current scope.


Read More - Know More







Post a Comment

Post a Comment (0)

Previous Post Next Post