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'