March 2, 2010 at 7:33 am
Hello,
I'm having an hard time using the merge statement.
Inside of it we're having WHEN MATCHED THEN UPDATE
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT
so a standard case.
At the end the merge statement we're outputting the inserted values using the OUTPUT command.
So I'm expecting to have insert values from the INSERT and UPDATE merge command only.
However when using only the WHEN NOT MATCHED BY SOURCE THEN DELETE section of the merge (other sections are commented) the inserted table outputted by the OUTPUT list a row full of NULL for the deleted row (in my test case, only one row is affected which is deleted by the merge).
I wasn't expecting that kind of behavior. Is there something I've not understood about INSERTED table or is there a way the merge command work which output a row for deleted data using all nulls?
Oh and there's no triggers and no referential integrity check on that table (no update / delete cascade).
The test code look like this (tables and IDs were renamed):
MERGE INTO Table1 AS target
USING (SELECT * FROM Table2) AS source
ON (target.ID = source.ID AND
target.ID2 = source.ID2)
WHEN NOT MATCHED BY SOURCE AND target.ID IN (SELECT ID FROM Table2) THEN
DELETE
OUTPUT
Inserted.*
INTO @invInsertTable;
SELECT * FROM @invInsertTable
@invInsertTable shows one row, each column are NULL
The row from Table1 is deleted after the process
March 4, 2010 at 12:05 pm
I've done a T-SQL sample to show what I mean:
Why NULLs are return on the DELETE statement from the Inserted table? I've not inserted anything!
Is it a "by design" feature of the merge statement or a bug?
(and there's no way to filter the $action statement when inserting into the variable table. If you need to insert into a table which NULLs are not allowed you got stuck up and it crashed)
DECLARE @TableTarget AS TABLE (
ID INT IDENTITY(1,1)
, ID1 INT
, ID2 INT
, RowIndex INT
, txtValue NVARCHAR(50)
)
DECLARE @TableSource AS TABLE (
ID INT IDENTITY(1,1)
, ID1 INT
, ID2 INT
, RowIndex INT
, txtValue NVARCHAR(50)
)
DECLARE @OutputTable AS TABLE (
ID INT
, ID1 INT
, ID2 INT
, RowIndex INT
, txtValue NVARCHAR(50)
, outputType NVARCHAR(10)
)
INSERT INTO @TableTarget VALUES (1, 1, 1, 'Txt1')
INSERT INTO @TableTarget VALUES (1, 2, 2, 'Txt2')
INSERT INTO @TableTarget VALUES (1, 3, 3, 'Txt3')
INSERT INTO @TableTarget VALUES (2, 1, 1, 'Txt4')
INSERT INTO @TableTarget VALUES (2, 2, 2, 'Txt5')
INSERT INTO @TableTarget VALUES (4, 1, 1, 'Txt6')
INSERT INTO @TableTarget VALUES (4, 2, 2, 'Txt7')
INSERT INTO @TableSource VALUES (2, 2, 1, 'NewTxt52')
SELECT * FROM @TableTarget
MERGE INTO @TableTarget AS TARGET
USING (SELECT * FROM @TableSource) AS SOURCE
ON (TARGET.ID1 = SOURCE.ID1 AND TARGET.ID2 = SOURCE.ID2)
WHEN NOT MATCHED BY SOURCE AND TARGET.ID1 IN (SELECT ID1 FROM @TableSource) THEN
DELETE
/*WHEN MATCHED THEN
UPDATE SET
TARGET.ID1 = SOURCE.ID1
, TARGET.ID2 = SOURCE.ID2
, TARGET.RowIndex = SOURCE.RowIndex
, TARGET.txtValue = SOURCE.txtValue*/
OUTPUT Inserted.*, $action
INTO @OutputTable;
SELECT * FROM @TableTarget
SELECT * FROM @OutputTable
I'm using SQL Server 2008 SP1 with standard configuration options
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply