jeudi 5 mars 2015

Insert into multiple tables using a single stored procedure


I have four tables, in all the four tables I have similar columns. I want to make an entry into each table using a single stored procedure. I have created a user defined table type:



CREATE TYPE dbo.tbluserdefined AS TABLE
(
SlNo int null,
[Source] nvarchar(200) null,
Destination nvarchar(200) null,
SourceID nvarchar(50) null,
DestinationID nvarchar(50) null,
BusID varchar(200) null,
Operator nvarchar(200) null,
TravelsName nvarchar(MAX) null,
DepartureTime nvarchar(50) null,
DepTimeMinute nvarchar(50) null,
ArrivalTime nvarchar(50) null,
ArrTimeMinute nvarchar(50) null,
Duration nvarchar(50) null,
SeatsAvilable nvarchar(50) null,
SeatFare nvarchar(50) null,
BusType nvarchar(MAX) null,
TravelDate nvarchar(200) null,
UpdatedOn nvarchar(200) null
);


The procedure is:



ALTER PROCEDURE dbo.Insert_Routestables
@ID INT, @tblRoutes dbo.tbluserdefined READONLY
AS
BEGIN
SET NOCOUNT ON;

IF (@ID=1)
begin
INSERT INTO dbo.tbl_RoutesUpdate_Bitla(SlNo, [Source], Destination, SourceID, DestinationID, BusID, Operator, TravelsName, DepartureTime, DepTimeMinute, ArrivalTime, ArrTimeMinute, Duration, SeatsAvilable, SeatFare, BusType, TravelDate, UpdatedOn )
SELECT
SlNo, [Source], Destination, SourceID, DestinationID,
BusID, Operator, TravelsName, DepartureTime, DepTimeMinute,
ArrivalTime, ArrTimeMinute, Duration, SeatsAvilable, SeatFare,
BusType, TravelDate, UpdatedOn
FROM
@tblRoutes
end

IF (@ID=2)
begin
INSERT INTO dbo.tbl_RoutesUpdate_Durgamba(SlNo, [Source], Destination, SourceID, DestinationID, BusID, Operator, TravelsName, DepartureTime, DepTimeMinute, ArrivalTime, ArrTimeMinute, Duration, SeatsAvilable, SeatFare, BusType, TravelDate, UpdatedOn)
SELECT
SlNo, [Source], Destination, SourceID, DestinationID,
BusID, Operator, TravelsName, DepartureTime, DepTimeMinute,
ArrivalTime, ArrTimeMinute, Duration, SeatsAvilable, SeatFare,
BusType, TravelDate, UpdatedOn
FROM
@tblRoutes
end

IF (@ID=3)
begin
INSERT INTO dbo.tbl_RoutesUpdate_Herms(SlNo, [Source], Destination, SourceID, DestinationID, BusID, Operator, TravelsName, DepartureTime, DepTimeMinute, ArrivalTime, ArrTimeMinute, Duration, SeatsAvilable, SeatFare, BusType, TravelDate, UpdatedOn)
SELECT
SlNo, [Source], Destination, SourceID, DestinationID,
BusID, Operator, TravelsName, DepartureTime, DepTimeMinute,
ArrivalTime, ArrTimeMinute, Duration, SeatsAvilable, SeatFare,
BusType, TravelDate, UpdatedOn
FROM
@tblRoutes
end

IF (@ID=4)
begin
INSERT INTO dbo.tbl_RoutesUpdate_Vrl(SlNo, [Source], Destination, SourceID, DestinationID, BusID, Operator, TravelsName, DepartureTime, DepTimeMinute, ArrivalTime, ArrTimeMinute, Duration, SeatsAvilable, SeatFare, BusType, TravelDate, UpdatedOn)
SELECT
SlNo, [Source], Destination, SourceID, DestinationID,
BusID, Operator, TravelsName, DepartureTime, DepTimeMinute,
ArrivalTime, ArrTimeMinute, Duration, SeatsAvilable, SeatFare,
BusType, TravelDate, UpdatedOn
FROM
@tblRoutes
END
END


I want data to be inserted into one table at a time, is there any solution for this, kindly help.





Aucun commentaire:

Enregistrer un commentaire