MERGE with ANSI

  • Challenging question. Thanks for submitting.

    http://brittcluff.blogspot.com/

  • My brain almost exploded..lol!! Pretty hard question. I actually have to open my book and read more about MERGE statement. I was under impression that this MERGE functionality came in SQL2008 only since I start using it in SQL2008 only. I didn't get lots of opportunities to play with newer functionality in 2005.

    One thing I would like to point out is that MERGE statement does allows BY SOURCE and BY TARGET in the statement. Here's the snippet of code where I use both:

    MERGE dbo.column_collection AS cc

    USING dbo.staging_column_collection AS scc

    ON (

    scc.table_name = cc.table_name

    AND scc.database_name = cc.database_name

    AND scc.ordinal_position = cc.ordinal_position

    AND scc.record_status = cc.record_status

    AND scc.schema_name = cc.schema_name

    AND cc.record_status = 'A'

    AND scc.platform_type_code = cc.platform_type_code

    AND scc.database_name NOT LIKE '_broken%'

    )

    WHEN MATCHED AND cc.physical_column_name <> scc.physical_column_name

    OR cc.data_type <> scc.data_type

    OR cc.column_length <> scc.column_length

    THEN UPDATE

    SET cc.physical_column_name = scc.physical_column_name

    , cc.column_name = scc.column_name

    , cc.data_type = scc.data_type

    , cc.column_length = scc.column_length

    WHEN NOT MATCHED BY TARGET THEN

    INSERT

    (

    column_name

    , physical_column_name

    , data_type

    , column_length

    , column_definition

    , record_status, insert_date

    , table_name, database_name

    , ordinal_position

    , schema_name

    , platform_type_code

    )

    VALUES

    (

    scc.column_name

    , scc.physical_column_name

    , scc.data_type

    , scc.column_length

    , scc.column_definition

    , scc.record_status

    , scc.insert_date

    , scc.table_name

    , scc.database_name

    , scc.ordinal_position

    , schema_name

    , platform_type_code

    )

    WHEN NOT MATCHED BY SOURCE

    THEN UPDATE

    SET cc.record_status = 'D'

Viewing 2 posts - 16 through 16 (of 16 total)

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