Post reply

use merge

  • January 10, 2012 at 1:59 pm

    #251260

    When I use merge statement in sql 2008 to do some records comparison from staging table A to production database table B,

    I use below:

    MERGE domCategory AS target

    USING staging.dbo.Category AS Source

    ON target.CategoryID=Source.CategoryID

    WHEN MATCHED AND target.CategoryDescription<>source.CategoryDescription Then

    UPDATE SET target.CategoryDescription=SOURCE.CategoryDescription

    But I found out it missed our records if target is null, but source value is null, I understand later this is because when I do the comparison I use <> this missed out null values.

    How can I solve this problem?

    Thanks

  • January 10, 2012 at 3:09 pm

    #1431454

    Try this:

    CREATE TABLE #domCategory(CategoryId INT,CD VARCHAR(40))

    INSERT INTO #domCategory

    SELECT 1, NULL UNION ALL

    SELECT 2, 'something'

    CREATE TABLE #Category(CategoryId INT, CD VARCHAR(40))

    INSERT INTO #Category

    SELECT 1,'Yes' UNION ALL

    SELECT 2,'no'

    MERGE #domCategory AS target

    USING #Category AS Source

    ON target.CategoryID=Source.CategoryID

    WHEN MATCHED AND target.CD <> source.CD

    OR target.CD IS NULL Then --Added to statement

    UPDATE SET target.CD=SOURCE.CD;

    SELECT * FROM #domCategory

    --Data in #domCategory BEFORE MERGE

    CategoryId CD

    1 NULL

    2 something

    --AFTER MERGE

    1 Yes

    2 no

    Is this what you desired ?

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • January 10, 2012 at 4:38 pm

    #1431498

    Personal preference to some extent, but I prefer this:

    MERGE domCategory AS target

    USING staging.dbo.Category AS source ON

    target.CategoryID = source.CategoryID

    WHEN MATCHED

    AND NOT EXISTS

    (

    SELECT target.CategoryDescription

    INTERSECT

    SELECT source.CategoryDescription

    )

    THEN UPDATE

    SET target.CategoryDescription = source.CategoryDescription;

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • January 10, 2012 at 5:32 pm

    #1431518

    Thanks both,

    To bitbucket-25253

    I think your added statment only considers the target is null,

    But how about the source end is Null, no matter what, I would like to overwrite target with source.

    To SQL Kiwi,

    could you explain a little bit about what this not exists, and intersect does.

    Actually I have more columns want to check, in the post I just wanted to make easier by using only categorydescription, if this still works if I have more columns to compare for example categoryType, categoryDate.

  • January 10, 2012 at 5:46 pm

    #1431522

    sqlfriends (1/10/2012)

    could you explain a little bit about what this not exists, and intersect does.

    So glad you asked:

    http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • January 10, 2012 at 6:42 pm

    #1431533

    Wow, that is very good article that helps me about handling null values.

    But I need to spend some time to study it. and try on my database.

    I may check back.

    Thanks a lot

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

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