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.
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:
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