Is it possible to use a merge statement inside a SQL cursor using MS SQL 2017 and better? The source would be the result of the cursor and the target would be a table. I have a table called JOB_IMPORT_LOG and a source table in linked SQL instance called JOBS. My cursor and loop are defined as below. Is this correct? Is there a more efficient way to construct this?
-- Declare cursor to locate all jobs that have not been inserted into GP yet
DECLARE db_cursor CURSOR FOR
SELECT JOBNUMBER,CONTRACTNUMBER,ISNULL(CRDATE,'19010101') AS CRDATE,CONVERT(CHAR(10),ISNULL(AGENCY,'')) AS AGENCY,ISNULL(CONTRACTAMT,0.00) AS CONTRACTAMT,
ISNULL(PaymentOffice,'NONE') AS PAYMENTOFFICE,ISNULL(BillToCode,'NONE') AS BILLTOCODE
FROM [OTHERSERVER].[OTHERDATABASE].[dbo].[JOBS] J
LEFT OUTER JOIN [OTHERSERVER].[OTHERDATABASE].[dbo].[SHIPTO] S ON J.PaymentOffice=S.SHIPID
WHERE JOBNUMBER NOT IN (SELECT JOBNUMBER FROM JOBTABLE)
ORDER BY AGENCY,JOBNUMBER
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @I_PAcontid,@I_PACONTNUMBER,@I_CRDATE,@I_CUSTNMBR,@I_CONTRACTAMT,@I_PAYMENTOFFICE,@I_BILLTOCODE
WHILE @@FETCH_STATUS = 0
BEGIN
-- Update the row if it exists.
MERGE [dbo].[JOB_IMPORT_LOG] AS target
USING (SELECT @I_PAcontid) AS source (JOBNUMBER)
ON (target.JOBNUMBER = source.JOBNUMBER)
WHEN MATCHED THEN
UPDATE SET [LASTMODDATE] = CONVERT(DATE, GETDATE()),[LASTMODTIME] = CONVERT(TIME(0),GETDATE())
WHEN NOT MATCHED THEN
INSERT ([LOADDATE],[LOADTIME],[JOBNUMBER],[CONTRACTNUMBER],[CRDATE],[AGENCY],[CONTRACTAMOUNT],[PAYMENTOFFICE],[BILLTOCODE],[LASTMODDATE],[LASTMODTIME])
VALUES
(CONVERT(DATE, GETDATE()),CONVERT(TIME(0),GETDATE()),@I_PAcontid,@I_PACONTNUMBER,@I_CRDATE,@I_CUSTNMBR,@I_CONTRACTAMT,@I_PAYMENTOFFICE,@I_BILLTOCODE
,CONVERT(DATE, GETDATE()),CONVERT(TIME(0),GETDATE()));
FETCH NEXT FROM db_cursor INTO @I_PAcontid,@I_PACONTNUMBER,@I_CRDATE,@I_CUSTNMBR,@I_CONTRACTAMT,@I_PAYMENTOFFICE,@I_BILLTOCODE
END
END;
Open in new window
but yes there is more efficient way to do this. I will send you soon in a separate comment.