June 17, 2011 at 1:57 pm
I have a situation where our data pump
1) copies (saves) rows into a history table if it's going to be updated by our new source.
2) updates the rows in the destination table from the source table, and finally
3) inserts the new rows from source into the destination.
I see this as a good candidate for rewriting into a MERGE statement; but I have a 'best practices' question.
The only way I see I can do this is in two steps:
MERGE into Destination as D
USING Source as S
ON S.pk1 = D.pk1
WHEN NOT MATCHED THEN INSERT VALUES (S.pk1, S.col1)
WHEN MATCHED THEN UPDATE SET col1 = S.col1
OUTPUT $action as 'Action', DELETED.pk1, DELETED.col1 into @tblVar
INSERT into History
SELECT pk1, col1
FROM @tblVar
WHERE Action = 'UPDATE'
Am I missing something here? Is there a way to update the History table in the same statement as the merge? Is there a better way to accomplish my goal? I'm guessing not because as near as I can tell OUTPUT is putting a row into @tblVar for all of the inserts as well (in the case above it would just be NULLs).
June 17, 2011 at 2:06 pm
No sooner than I typed this I read an article by Adam Machanic that seemed to wrap everything into one...
Essentially it looks like I can do....
INSERT into History
SELECT pk1, col1
FROM [previously mentioned merge code using OUTPUT (minus the INTO clause)]
WHERE Action = UPDATE.
???? Yes?
June 17, 2011 at 3:08 pm
BobMcC (6/17/2011)
No sooner than I typed this I read an article by Adam Machanic that seemed to wrap everything into one...Essentially it looks like I can do....
INSERT into History
SELECT pk1, col1
FROM [previously mentioned merge code using OUTPUT (minus the INTO clause)]
WHERE Action = UPDATE.
???? Yes?
Looks right. Neat technique.
It won't affect you using it for the problem stated but from the article there is this gotcha: Note that as of SQL Server 2008 the results of the outer operation must be an INSERT-SELECT. That's a rather frustrating limitation, and I really hope the SQL Server team loosens the restrictions in the next version of the product.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 10:02 am
opc.three (6/17/2011)
Looks right. Neat technique.It won't affect you using it for the problem stated but from the article there is this gotcha: Note that as of SQL Server 2008 the results of the outer operation must be an INSERT-SELECT. That's a rather frustrating limitation, and I really hope the SQL Server team loosens the restrictions in the next version of the product.
The limitation was noticed; as a matter of fact that's the technique I used was as INSERT - SELECT. Pretty cool if you as me! 🙂
June 20, 2011 at 10:05 am
That's why I said "It won't affect you" ... you lucked out that it fit your use-case 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply