merge and collate [Resolved] - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

merge and collate [Resolved]

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-05-09 : 09:19:04

Hi,
how can I add COLLATE DATABASE_DEFAULT into a merge statement?

Looking everywhere and haven't found it yet.
My code:

;WITH myCTE AS (
SELECT
a,b,c
FROM myTable

)
MERGE dbo.[T_table] AS Target
USING myTable AS Source
ON (Target.a= Source.a)
WHEN NOT MATCHED BY TARGET
THEN INSERT(a, b) VALUES(Source.a, Source.b)
WHEN MATCHED
THEN UPDATE SET Target.b= Source.b
WHEN NOT MATCHED BY SOURCE
THEN DELETE

;

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-09 : 13:30:28

Not sure what you're trying to accomplish. Is that you are trying to modify the collation of a specific value during an insert/update?

Be One with the Optimizer
TG

lappin
Posting Yak Master

182 Posts

Posted - 2011-05-10 : 05:57:35

There is a collation conflict. I am using a linked server as source. My table and the source table use different collations. So I want to use COLLATE DATABASE_DEFAULT on both sides of the join between source and target. I know how to do this in a normal query inside join or where statement, but I don't know the syntax inside a merge statement.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-10 : 06:23:37

Untested, but it parses successfully.

MERGE dbo.[T_table] AS Target
USING myTable AS Source
ON (Target.a= Source.a COLLATE DATABASE_DEFAULT)
WHEN NOT MATCHED BY TARGET
THEN INSERT(a, b) VALUES(Source.a, Source.b)
WHEN MATCHED
THEN UPDATE SET Target.b= Source.b
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

--
Gail Shaw
SQL Server MVP

lappin
Posting Yak Master

182 Posts

Posted - 2011-05-10 : 10:29:38

Worked great thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources