MERGE with ANSI

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Hugo Kornelis » MERGE with ANSI

17 posts, Page 2 of 2

««1 2

MERGE with ANSI

Rate Topic

Display Mode

Topic Options

Author

Message

Koen Verbeeck

Posted Sunday, November 27, 2011 11:27 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 9,378, Visits: 6,473

Tough one, thanks.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1212283

Anipaul

Posted Monday, November 28, 2011 12:08 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 1:35 AM
Points: 4,789, Visits: 1,336

A very tough question....

Post #1212292

Revenant

Posted Monday, November 28, 2011 9:18 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 3,462, Visits: 2,538

I have unbfair advantage: MERGE is my daily bread. Wink

Thanks for this interesting question!

Post #1212578

SQLRNNR

Posted Monday, November 28, 2011 9:26 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754, Visits: 12,337

Thanks Hugo

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1212585

OzYbOi d(-_-)b

Posted Monday, November 28, 2011 4:10 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778

thanks Hugo!

Post #1212808

Britt Cluff

Posted Tuesday, November 29, 2011 6:17 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:17 PM
Points: 1,558, Visits: 247

Challenging question. Thanks for submitting.

http://brittcluff.blogspot.com/

Post #1213157

sbamaty

Posted Thursday, December 29, 2011 3:15 PM

SSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 11:03 AM
Points: 628, Visits: 102

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'

Post #1228110

« Prev Topic | Next Topic »

17 posts, Page 2 of 2

««1 2

Permissions