Search code examples
sqlt-sqlmerge-statementoutput-clause

SQL Merge Output Clause Error

I have three tables:

  1. source table
  2. ODS table
  3. Staging table

My merge statement uses source table to insert data into the ODS and Outputs into Staging table. After insert, the count of source table and ODS is the same, however, staging count is less than both. The output clause is supposed to insert a copy of what is being inserted in ODS into Staging but it's not the case. Does anybody know why that's the case? My merge statement is as follows:-

BEGIN TRANSACTION
BEGIN TRY
/* truncate staging table */
TRUNCATE TABLE stage table 

/* merge into ODS based on NK */
MERGE INTO ODS table as TRG
USING source table as SRC
/* ON Natural Key for that table/data type */
    ON TRG.column = SRC.column

/* insert new records into ODS */
WHEN NOT MATCHED AND SRC.column = @LOB THEN
    INSERT (columns )
    VALUES ( columns )

OUTPUT INSERTED.* INTO STG. table

COMMIT TRANSACTION

Thanks!

Solution

  • SQL Merge Output Clause Error

    The problem was that the variable @LOB was a stored procedure variable so whenever I run the stored procedure by LOB it was truncating the staging table of the previous LOB data hence why the staging table had less data than both the source and target table.It's resolved now.