SQL Server query, stored procedure multiple Identity_Insert of tables creating archived record

Question

BEGIN
IF @TransType = 1
BEGIN
       SET IDENTITY_INSERT [dbo].[Sales.DeletedDeliveryHeaderHistory] ON 

       -- INSERT TO DELETE DELIVERY HEADER HISTORY
       INSERT INTO @DRHeaderHist (RowID, CompanyName, ItemClassCode, DeliveryDate,
                                  LoadingDate, DRNumber, Custnmbr, CustName,    
                                  TruckerID, PlateNumberID, TonnerID, DeliveryTypeID,
                                  Remarks, DateCreated, DateModified, DatePosted, 
                                  UserName, FGUserName, DRUserName, PostedFG, 
                                  Import, CareOf, CareOfName )
           SELECT * 
           FROM [Sales.DeliveryHeaderHistory] 
           WHERE DRNUMBER = @DR

       INSERT INTO dbo.Sales.DeletedDeliveryHeaderHistory (RowID, CompanyName, 
                       ItemClassCode,  DeliveryDate, LoadingDate, DRNumber, Custnmbr, 
                       CustName, TruckerID, PlateNumberID, TonnerID, DeliveryTypeID,
                       Remarks, DateCreated, DateModified, DatePosted, UserName, 
                       FGUserName, DRUserName, PostedFG, Import, CareOf, CareOfName )
           SELECT * 
           FROM @DRHeaderHist

SET IDENTITY_INSERT [dbo].[Sales.DeletedDeliveryHeaderHistory] OFF 
SET IDENTITY_INSERT [dbo].[Sales.DeletedDeliveryDetailsHistory] ON 

-- INSERT TO DELETE DELIVERY DETAIL HISTORY

INSERT INTO @DRDetailsHist ( RowID, CompanyName, DRNumber, OrderDocNum, OFLineNumber, LineNumber, Quantity, UofM, KgsPerBag, Itemnmbr,
                                                  TotalKgs, ItemDesc, VariantCode, VariantDesc, ProductionCode, OrderDate, DateNeeded, Week, Month1, PostedFG )
SELECT * from [dbo].[Sales.DeliveryDetailsHistory] WHERE DRNUMBER = @DR


INSERT INTO [dbo].[Sales.DeletedDeliveryDetailsHistory] ( RowID, CompanyName, DRNumber, OrderDocNum, OFLineNumber, LineNumber, Quantity, UofM, KgsPerBag, Itemnmbr,
                                                  TotalKgs, ItemDesc, VariantCode, VariantDesc, ProductionCode, OrderDate, DateNeeded, Week, Month, PostedFG )
SELECT * FROM @DRDetailsHist

SET IDENTITY_INSERT [dbo].[Sales.DeletedDeliveryDetailsHistory] OFF 
END
END

Is this possible in a stored procedure query because I'm getting an error

Msg 208, Level 16, State 1, Procedure SFC_spTransferDRtoDeleteArchives, Line 85
Invalid object name 'dbo.Sales.DeletedDeliveryHeaderHistory'.

I'm trying to find out a solution for creating a stored procedure for a multiple transfer of data on a multiple tables in one stored procedure just want to know if its possible? or there might be solution for this or not..

Answer 1

You have a syntax error in this script. But I have no idea why you would embed a period in the name of a table. You need to use square brackets more judiciously:

IF @TransType = 1

BEGIN

SET IDENTITY_INSERT [dbo].[Sales.DeletedDeliveryHeaderHistory] ON 

-- INSERT TO DELETE DELIVERY HEADER HISTORY

INSERT INTO @DRHeaderHist (RowID, CompanyName, ItemClassCode, DeliveryDate, LoadingDate, DRNumber, Custnmbr, CustName,  TruckerID, PlateNumberID, TonnerID, DeliveryTypeID,
                            Remarks, DateCreated, DateModified, DatePosted, UserName, FGUserName, DRUserName, PostedFG, Import, CareOf, CareOfName )
SELECT * from [Sales.DeliveryHeaderHistory] WHERE DRNUMBER = @DR



----------------v
INSERT INTO dbo.[Sales.DeletedDeliveryHeaderHistory] (RowID, CompanyName, ItemClassCode,  DeliveryDate, LoadingDate, DRNumber, Custnmbr, CustName, TruckerID, PlateNumberID, TonnerID, DeliveryTypeID,
                                                  Remarks, DateCreated, DateModified, DatePosted, UserName, FGUserName, DRUserName, PostedFG, Import, CareOf, CareOfName )
SELECT * FROM @DRHeaderHist

SET IDENTITY_INSERT [dbo].[Sales.DeletedDeliveryHeaderHistory] OFF 
SET IDENTITY_INSERT [dbo].[Sales.DeletedDeliveryDetailsHistory] ON 

-- INSERT TO DELETE DELIVERY DETAIL HISTORY

INSERT INTO @DRDetailsHist ( RowID, CompanyName, DRNumber, OrderDocNum, OFLineNumber, LineNumber, Quantity, UofM, KgsPerBag, Itemnmbr,
                                                  TotalKgs, ItemDesc, VariantCode, VariantDesc, ProductionCode, OrderDate, DateNeeded, Week, Month1, PostedFG )
SELECT * from [dbo].[Sales.DeliveryDetailsHistory] WHERE DRNUMBER = @DR




INSERT INTO [dbo].[Sales.DeletedDeliveryDetailsHistory] ( RowID, CompanyName, DRNumber, OrderDocNum, OFLineNumber, LineNumber, Quantity, UofM, KgsPerBag, Itemnmbr,
                                                  TotalKgs, ItemDesc, VariantCode, VariantDesc, ProductionCode, OrderDate, DateNeeded, Week, Month, PostedFG )
SELECT * FROM @DRDetailsHist

SET IDENTITY_INSERT [dbo].[Sales.DeletedDeliveryDetailsHistory] OFF 

END

I noted where the square brackets are needed.