Avatar of rwheeler23
rwheeler23Flag for United States of America

asked on 

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

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;
Microsoft SQL Server

Avatar of undefined
Last Comment
rwheeler23
Avatar of Máté Farkas
Máté Farkas
Flag of Hungary image

Your code looks fine but I recommend to declare cursor as static:
DECLARE db_cursor CURSOR STATIC FOR 

Open in new window

but yes there is more efficient way to do this. I will send you soon in a separate comment.
ASKER CERTIFIED SOLUTION
Avatar of Máté Farkas
Máté Farkas
Flag of Hungary image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of rwheeler23
rwheeler23
Flag of United States of America image

ASKER

Excellent solution. May I ask what does declaring the cursor as static do?
I do try to use cursors as little as possible. I will adopt your technique going forward.
Static cursor creates a simple copy of the result set in tempdb (like in temp table). This will not lock records for other users and you can quickly steps over rows, however you cannot update rows throught the cursor.

If you have a dynamic cursor then there is no copy of data into the tempdb but the query behind the cursor is executed again on row by row. You can update the current row with the cursor but this means that rows are locked while the cursor is in opened state.

Avatar of rwheeler23
rwheeler23
Flag of United States of America image

ASKER

Thank you for your excellent solution and description.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo