troubleshooting Question

Is it possible to use a sql merge statement inside a sql cursor?

Avatar of rwheeler23
rwheeler23Flag for United States of America asked on
Microsoft SQL Server
5 Comments1 Solution20 ViewsLast Modified:
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;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros