How to filter the output clause from Merge
-
I have over 30 procedures using queries of the following form:
MERGE SomeTable gUSING (
SELECT @user-id AS UserId, @YrId AS YrId) AS s
ON s.UserID = g.UserId AND s.YrId = g.YrId
WHEN MATCHED
THEN UPDATE SET
SomeData = @p1
,SomeOtherData= @p2
,Entered_By = @Updater
,Entry_Date = GETDATE()
WHEN NOT MATCHED
THEN INSERT (
UserID
,SomeData
,SomeOtherData
,Entered_By
,Entry_Date
,YrID)
VALUES (
,@p1
,@p2
,@Updater
,GETDATE()
,@PlanYrID)
OUTPUT deleted.* INTO SomeTable_History;
SS 2008 has just become available. I've been rewriting upsert procedures as MERGE statements. I initially wrote and tested a MERGE statement without the output clause, but realized I had to meet the history table requirement. So I added the output clause and tested it.
I now realize that I tested the insert condition before the output clause was added, then tested only the update condition to make sure the output clause worked. I expected that when an insert occurred, no output row would exist because I was outputting from Deleted. I now realize that's incorrect. The inserted rows have an output row, but every column in Deleted is NULL.
The history tables have few constraints, but they can't (and shouldn't) receive rows consisting entirely of NULL values. Every insert is now failing due to not null constraints on the history tables.
I need to fix this (over 30 times) quickly, and hopefully before other developers realize that all inserts are failing (no, this isn't production, just a dev envrionment, but it's shared).
I'm not allowed to use triggers (corporate environment). How do I modify these queries to insert only rows that are updated, and not rows that are inserted?
My initial thought is to output into a table variable and then insert to history from that table variable where the key columns are not null, but I'm wondering if that's the best approach.
-
Here is a case that would be better not using Merge. I would just have two separate statements, one the updates rows and one that inserts based on the join. The use output appropriately.
The probability of survival is inversely proportional to the angle of arrival.
-
I've always used a local temp table to capture OUTPUT.
Scanning BOL, you can't OUTPUT to a remote view - but what about a 'local' one? Use $action and filter on it.
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
I decided to use a table variable. I sent the output into a table variable, then inserted into the history table where the key is not null. I could have used $action, but didn't really need it. The primary key output from an updated row will be not null, and from an inserted row will be null.
I really wanted to put a where clause on the output clause, but that just isn't in there.
I also wish there was a way to declare a table variable and have its column structure exactly match another table's structure without writing out all the columns and types.
-
Stephanie Giovannini (1/3/2012)
...I also wish there was a way to declare a table variable and have its column structure exactly match another table's structure without writing out all the columns and types.That's one of the many reasons I prefer #temp tables to table variables:
SELECT ... INTO #Temp FROM MyTable WHERE 0=1
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Just to say something else.
SELECT TOP (0) * INTO #tmp FROM dbo.table
:hehe:
-
The MERGE statement can OUTPUT directly to an INSERT statement (this is sometimes referred to as 'composable DML'). You can add the required filtering to the INSERT statement's WHERE clause as usual:
USE Sandpit -- my test database
GO
CREATE TABLE dbo.Upsert
(
UserID INTEGER NOT NULL,
YearID SMALLINT NOT NULL,
SomeData CHAR(1) NOT NULL,
Modified DATETIME NOT NULL
PRIMARY KEY (UserID, YearID)
)
GO
CREATE TABLE dbo.History
(
UserID INTEGER NOT NULL,
YearID SMALLINT NOT NULL,
SomeData CHAR(1) NOT NULL,
Modified DATETIME NOT NULL
)
-- Add a row, no history generated
DECLARE
@user-id INTEGER = 1,
@YearID INTEGER = 2012,
@SomeData CHAR(1) = 'A'
INSERT dbo.History
(UserID, YearID, SomeData, Modified)
SELECT
M.UserID,
M.YearID,
M.SomeData,
M.Modified
FROM
(
MERGE Upsert AS U
USING (VALUES(@UserID, @YearID, @SomeData)) AS S (UserID, YearID, SomeData) ON
S.UserID = U.UserId
AND S.YearID = U.YearID
WHEN MATCHED THEN
UPDATE SET
SomeData = @SomeData,
Modified = GETDATE()
WHEN NOT MATCHED THEN
INSERT
(UserID, YearID, SomeData, Modified)
VALUES
(@UserID, @YearID, @SomeData, GETDATE())
OUTPUT
$action,
DELETED.UserID,
DELETED.YearID,
DELETED.SomeData,
DELETED.Modified
) AS M
WHERE
M.[$action] = N'UPDATE';
-- Update a row, history generated
DECLARE
@user-id INTEGER = 1,
@YearID INTEGER = 2012,
@SomeData CHAR(1) = 'B'
INSERT dbo.History
(UserID, YearID, SomeData, Modified)
SELECT
M.UserID,
M.YearID,
M.SomeData,
M.Modified
FROM
(
MERGE Upsert AS U
USING (VALUES(@UserID, @YearID, @SomeData)) AS S (UserID, YearID, SomeData) ON
S.UserID = U.UserId
AND S.YearID = U.YearID
WHEN MATCHED THEN
UPDATE SET
SomeData = @SomeData,
Modified = GETDATE()
WHEN NOT MATCHED THEN
INSERT
(UserID, YearID, SomeData, Modified)
VALUES
(@UserID, @YearID, @SomeData, GETDATE())
OUTPUT
$action,
DELETED.UserID,
DELETED.YearID,
DELETED.SomeData,
DELETED.Modified
) AS M
WHERE
M.[$action] = N'UPDATE';
-- Show the contents of the tables
SELECT * FROM dbo.Upsert AS u
SELECT * FROM dbo.History AS h
-
SQL Kiwi (1/3/2012)
The MERGE statement can OUTPUT directly to an INSERT statement (this is sometimes referred to as 'composable DML').Thanks! I thought it should be possible in one statement!
-
SQL Kiwi (1/3/2012)
The MERGE statement can OUTPUT directly to an INSERT statement (this is sometimes referred to as 'composable DML'). You can add the required filtering to the INSERT statement's WHERE clause as usual:... And there's reasons I don't consider myself a guru. Paul, is there any secondary locking that can occur through that or does it hold the Merge locks until the final Insert is complete as a full implicit transaction?
And, yes, that's damned cool code right there. I think I have a few places I'll use it if we ever upgrade around here...
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Evil Kraig F (1/5/2012)
Paul, is there any secondary locking that can occur through that or does it hold the Merge locks until the final Insert is complete as a full implicit transaction?The whole statement is atomic, in a single query plan:
Like any statement that changes data, update/exclusive locks will be taken and held to the end of the transaction. If no explicit enclosing transaction exists, the transaction there is the implicit one wrapping the the single statement (this is what makes both the INSERT and the MERGE atomic). The inner statement that uses OUTPUT does not have to be MERGE, and there are a number of restrictions and caveats, documented in Books Online under the INSERT (Transact-SQL) entry (dml_table_source), and OUTPUT clause (Transact-SQL)
-
I've discovered that the composable DML approach only works on SQL Server 2008 R2, not SQL Server 2008. My production target is 2008, but I have R2 available on my machine and am working out of SSMS for R2.
SQL Server 2008 insists that there is a syntax error near whatever table name follows MERGE. Depending on the aliases used, the syntax error might be near '.', 'AS' or 'USING', whichever symbol first follows MERGE.
In R2, the same composable DML statement executes.
-
Stephanie Giovannini (1/9/2012)
I've discovered that the composable DML approach only works on SQL Server 2008 R2, not SQL Server 2008. My production target is 2008, but I have R2 available on my machine and am working out of SSMS for R2.Your 2008 database is set to a lower compatibility level. MERGE was new for SQL Server 2008, so it requires compatibility level 100.
Check the compatibility level of the context database with:
SELECT
d.[compatibility_level]
FROM sys.databases AS d
WHERE
d.name = DB_NAME()
-
Yep, compatibility level was the problem. All the databases in SS 2008 had been originally restored from SS 2005. My local sandbox was created new in SS 2008 R2.
Thanks!
-
Stephanie Giovannini (1/9/2012)
Yep, compatibility level was the problem. All the databases in SS 2008 had been originally restored from SS 2005. My local sandbox was created new in SS 2008 R2.You're welcome.
-
That was a very helpful DML, Paul. Is there any way to remove duplicates in the result of OUTPUT clause (I cannot make it work with DISTINCT or GROUP BY clause)?
thanks in advance!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply