Ronok Bhowmik

Ronok Bhowmik

  • 1.9k
  • 37
  • 26.6k

Stored Procedure to Insert Values in multiple Tables at once

Feb 15 2014 11:32 AM
Here is my sql query.I have three tables. they are Floors(FloorId(pk),FloorName),Blocks (FloorId(fk),BlockId(pk),BlockName) ,Rooms(BlockId(fk),RoomId(pk),RoomName) .I want to Insert Values in Floors,Blocks & Rooms Tables at once.
 
 
 
USE [NewDatabase]
GO
/****** Object: StoredProcedure [dbo].[uspinsertion] Script Date: 02/15/2014 12:47:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[uspinsertion]
( @FloorId int,
@FloorName nvarchar(20),
@BlockId int,
@BlockName nvarchar(20),
@RoomId int,
@RoomName nvarchar(20) )
as
begin
set nocount on
DECLARE @RecordID INT,@RecordID1 INT

insert into Floors(FloorId,FloorName)
Values(@FloorId,@FloorName)
SET @RecordID=Scope_Identity()
end

set @RecordID=@FloorId
insert into Blocks (FloorId,BlockId,BlockName)
Values (@RecordID,@BlockId,@BlockName)
set @RecordID=Scope_Identity()
end

Set @RecordID1=@BlockId
Insert Into Rooms(BlockId,RoomId,RoomName)
Values (@RecordID1,@RoomId,@RoomName)
SET @RecordID1=Scope_Identity()
Return

Brought to you by:

Answers (1)