How to filter the output clause from Merge

  • Stephanie Giovannini

    SSCertifiable

    Points: 7422

    I have over 30 procedures using queries of the following form:

    MERGE SomeTable g

    USING (

    SELECT @userid 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 (

    @userid

    ,@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.

  • sturner

    SSC-Insane

    Points: 22411

    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.

  • ChrisM@home

    SSC-Insane

    Points: 24260

    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]

  • Stephanie Giovannini

    SSCertifiable

    Points: 7422

    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.

  • ChrisM@home

    SSC-Insane

    Points: 24260

    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]

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Just to say something else.

    SELECT TOP (0) * INTO #tmp FROM dbo.table

    :hehe:

  • Paul White

    SSC Guru

    Points: 150341

    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

    @userid 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

    @userid 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

  • Stephanie Giovannini

    SSCertifiable

    Points: 7422

    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!

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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 Farrell

    Never 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

  • Paul White

    SSC Guru

    Points: 150341

    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)

  • Stephanie Giovannini

    SSCertifiable

    Points: 7422

    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.

  • Paul White

    SSC Guru

    Points: 150341

    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()

  • Stephanie Giovannini

    SSCertifiable

    Points: 7422

    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!

  • Paul White

    SSC Guru

    Points: 150341

    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.

  • pd

    Grasshopper

    Points: 12

    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!

    • This reply was modified 1 month ago by  pd.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply