I have a procedure for insert in multiple dependent tables (update in case record exist). I have input parameters as comma separated string which I am reading in table.
After 1st insertion I am getting InsertedIds in another table variable. I am struggling over how to do insert in 2nd table. I have following input parameters for 2nd table:
Declare @IdsToBeUpdated table (primary key identity pkey, id int) -- values are 1,-1,3,-1
Declare @CommentsTobeInserted table( primary key identity pkey, comment varchar (max)) -- values are 'com1', 'com2', 'com3'
-1 input in table @IdsToBeUpdated depicts insertion for the corresponding rows in all input tables and value other than -1 depicts that records at that pkey value in all other tables (@CommentsTobeInserted table as in example) have to be updated.
So after first insertion I am getting the inserted ids for rows with -1 value. @InsertedIds = 4,5
So my Ids list logically would become.. 1,4,3,5.
But I am stuck now how to insert/update records in second table respectively. 2nd table would be like follows:
Pkey Primary key identity, commentIds(coming from inserted ids and @IdsToBeUpdated), comments.
I have added one more InsertedIds column in @CommentsTobeInserted. If I could fill it with right InsertedId against correct row, I guess I would be able to do insert/update in 2nd table. And where the value is Id in new column I would do insert, where it's null, I would perform update using @IdsToBeUpdated table. But currently none of my approach is working. I am new to SQL. Any help would be highly appreciated.
Following is some portion of script for better understanding. I have added first insertion script as well.
USE [Demo]
GO
/****** Object: StoredProcedure [dbo].[USP_NewRequest_Insert] Script Date: 2/11/2016 2:50:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_NewRequest_Insert]
-----------------------------------
--------- SomeInput parameters
---------for first insertion omitted
--------------------------------
@IdsToBeUpdated varchar(MAX), --Values are 23|-1|32|-1|-1
@CommentList AS VARCHAR(MAX), --Values 'C2'|'C4'|'C5'|'C6'|'C7'
@MCodeList VARCHAR(MAX), --Values 'M2'|'M4'|'M5'|'M6'|'M7'
@CustomerIdsList VARCHAR(MAX), ----Values 'Cust2'|'Cust4'|'Cust5'|'Cust6'|'Cust7'
@ReturnValue AS INT OUTPUT,
------------------Data is in the order it has to be inserted.. where IDs exist in @IdsToBeUpdated, updation would be done. For -1 values, insertion.
AS
BEGIN
BEGIN TRANSACTION
--------------------------Split input strings aand insert in Table variable----------------------------
declare @MCodes Table (pkey int primary key identity, MCode varchar(20))
insert into @MCodes select s.Item from [dbo].UDF_SplitString(@MCodeList, '|') s
declare @CusCodes Table (pkey int primary key identity, CusCode varchar(200))
insert into @CusCodes select s.Item from [dbo].UDF_SplitString(@CustomerIdsList, '|') s
declare @ReqDetailsIds Table (pkey int primary key identity, Id Int)
insert into @ReqDetailsIds select Convert(INT,RTRIM(LTRIM(s.Item))) from [dbo].UDF_SplitString(@IdsToBeUpdated, '|') s
where s.Item is not null and RTRIM(LTRIM(s.Item)) <>''
declare @ProductComments Table (pkey int primary key identity, Comment Varchar(max), insertedId int null)
insert into @ProductComments(Comment) select s.Item from [dbo].UDF_SplitString(@CommentList, '|') s
DECLARE @intErrorCode int;
------------------------------------------------------------------------------------------------------------
-----------------First Insertion which returns inserted IDs for 2nd insertion
------------------------------------------------------------------------------------------------------------------
---Insert/Update product details in [RequestDetails]
Declare @InsertedIDList Table (pkey int primary key identity, ID int); --------Table to read inserted Ids. Used in query below
-----------------------Insert query in case Detail id = -1
INSERT INTO [dbo].[RequestDetails]
[MCode]
,[CustomerIds]
,[ExpectedVolume]------Some parameters coming for first insertion in input
,[StatusCode])
Output INSERTED.ReqDetailId Into @InsertedIDList(ID)
SELECT A.MCode, B.CusCode, E.Vol,1
FROM @MCodes A
JOIN @CusCodes B ON B.pkey = A.pkey
JOIN @ExpectedVols E ON E.pkey = A.pkey
JOIN @ReqDetailsIds G ON G.pkey = A.pkey
WHERE G.Id = -1 --If id = -1, insert
---------------------------Update Query for rest records
UPDATE [dbo].[RequestDetails]
SET [MCode] = upd.MCode
,[CustomerIds] = upd.CusCode
,[ExpectedVolume] = upd.ExVol
,[StatusCode] = 1
FROM(
SELECT A.MCode, B.CusCode, E.ExVol, G.Id
FROM @MCodes A
JOIN @CusCodes B ON B.pkey = A.pkey
JOIN @ExpectedVols E ON E.pkey = A.pkey
JOIN @ReqDetailsIds G ON G.pkey = A.pkey
WHERE G.Id <> -1
) upd
WHERE upd.Id = dbo.RequestDetails.ReqDetailId
IF(@@Error<>0)
BEGIN
SET @intErrorCode = @@Error
GOTO ERROR
END
ELSE
BEGIN
SET @ReturnValue=1
END
---------------------------------------------------------------------------
----------------------------Now similarly I have to do insert/update in Comments Table. But
----------------------------Comments table has RequestDetails Id column as foreign key. So
----------------------------now the challange is to add the rows with Inserted ID where value was = -1
----------------------------in input. We have got the IDs corresponding to -1 values from above insertion
----------------------------in the @InsertedIDList Table variable
-----------------------------------------------------------------------------------------
----------------------------Following is what I have tried so far. But I am not able to insert
----------------------------correct InsertedId against correct record.
----------------------------------------------------------------------------------------
-----------------------Here I tried to insert the new generated ids against corresponding comments in table variable.
-----------------------So that I can perform insert where value is not null. As NULL would be inserted where new ID has not been created
-----------------------and corresponding updated ID exists in input (Values not equal to -1)
-------------------------------------------------------------------------------------------------
Update @ProductComments set insertedId = i.ID from ---------This query is not working
(select A.pkey, B.id as detailId, row_number() over (order by (select 0)) as row_num from
@ProductComments A
JOIN @ReqDetailsIds B ON B.pkey = A.pkey) as mappedNewIds right join
@InsertedIDList i on i.pkey = mappedNewIds.row_num
where mappedNewIds.pkey = [@ProductComments].pkey
----Insert in CommentsTable for New Comments against request
---------------------------------
INSERT INTO [dbo].CommentsTable
( ReqDetailId, Comments, CreatedOn )
SELECT A.insertedId, A.Comment, GETDATE()
FROM @ProductComments A
where A.insertedId is not null
-----Update Query
------------------------------------------------------------------------------------------
UPDATE [dbo].[CommentsTable]
SET [ReqDetailId] = upd.Id
,[Comments] = upd.Comment
,[CreatedOn] = GetDate()
FROM(
SELECT A.Comment, B.Id
FROM @ProductComments A
JOIN @ReqDetailsIds B ON B.pkey = A.pkey
WHERE A.insertedId is not null
) upd
WHERE upd.Id = dbo.CommentsTable.ReqDetailId
END
select * from CommentsTable;
---------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
IF(@@Error<>0)
BEGIN
SET @intErrorCode = @@Error
GOTO ERROR
END
ELSE
BEGIN
SET @ReturnValue=1
END
COMMIT TRANSACTION
ERROR:
IF (@intErrorCode <> 0) BEGIN
SET @ReturnValue = 0;
ROLLBACK TRANSACTION
END
END
