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;
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.