0

I've no idea about transactions in SQL Manager (SSMS) in general. We have a query that we didn't expect to run as long as it did and we're wondering if it'd need to rollback changes if we cancelled it. It's a merge statement between two tables of which DBO.JEST has 700M rows and SAP.JEST has 200M. Our query has been running for 5 hours, and would be best if we cancelled it without it having to rollback since that would take a heck of a long time. Will the query rollback? Can we stop it from rolling back if it does?

Query:

MERGE [sap].JEST AS target
USING (SELECT * from dbo.JEST) AS source 
ON (
target.[MANDT]=source.[MANDT] AND
target.[OBJNR]=source.[OBJNR] AND
target.[STAT]=source.[STAT]
    )
WHEN MATCHED THEN 
    UPDATE SET 
        [MANDT]=source.[MANDT],
        [OBJNR]=source.[OBJNR],
        [STAT]=source.[STAT],
        [INACT]=source.[INACT],
        [CHGNR]=source.[CHGNR]
WHEN NOT MATCHED THEN
    INSERT ( 
        [MANDT],
        [OBJNR],
        [STAT],
        [INACT],
        [CHGNR]
         ) VALUES (
         source.[MANDT],
        source.[OBJNR],
        source.[STAT],
        source.[INACT],
        source.[CHGNR]
 );
Martian Marcera
  • 37
  • 5
  • Possible duplicate of [Does a T-SQL transaction get rolled back if cancelled?](https://stackoverflow.com/questions/54077279/does-a-t-sql-transaction-get-rolled-back-if-cancelled) – GSerg Feb 06 '19 at 14:04

1 Answers1

1

MERGE runs as a single statement having implicit transaction, so you don't need mention transaction explicitly to perform the required operation.

Will my query be required to “rollback” its changes if I cancel its execution

No, you don't need to call ROLLBACK, in case of any failure all the changes will automatically rolled back.

EDIT: If you want that when you terminate the query, it should not roll back. For this scenario, I can suggest that break your logic to run in batches, so that when you terminate the query only the current batch will get rolled back.

PSK
  • 17,547
  • 5
  • 32
  • 43
  • No I mean, that's my question. Will they be rolled back automatically? I might need to rephrase. Basically we don't want the query to rollback 'cause that may take so, so much longer than it already ran. It's been running for 5 hours now. – Martian Marcera Feb 06 '19 at 13:09
  • 2
    if you cancel the execution, all the changes will be rolled back. This is the default behaviour of Merge. If you don't want it to happen you can split the query to work on batches. So that if you terminate the query only the current batch request will be rolled back. – PSK Feb 06 '19 at 13:11
  • 2
    It is not possible to cancel a query and retain partial changes. The entire operation must be rolled back per ANSI/ISO SQL compliance with ACID ('A' is stands for atomic, all or none). – Dan Guzman Feb 06 '19 at 13:41