Merge Statement Error - 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 2012 Forums

Transact-SQL (2012)

Merge Statement Error

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-07-10 : 14:00:02

I have a simple merge statement that compares 2 tables and if the 2nd table does not have a matching entry I want the merge to insert the record.

Here is the statement:

MERGE
[SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100] AS Target
USING
[SALESLOGIX].[sysdba].[ACCOUNT] AS Source
ON
Target.ACCOUNTID = Source.AccountID
WHEN NOT MATCHED BY TARGET THEN
INSERT (ACCOUNTID,CREATEDATE,INDUSTRYPRACTICE) VALUES (Source.Accountid,getdate(),'TEST')
;
go

The error is:

Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.

Line 1: MERGE
Line 2: [SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100] AS Target

Any idias, and thank you in advance for your help.

Bryan Holmstrom

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-10 : 14:15:30

Target and Source are keywords that MERGE uses, so you should escape them like shown below:

MERGE 
[SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100] AS [Target]
USING 
[SALESLOGIX].[sysdba].[ACCOUNT] AS [Source]
ON 
[Target].ACCOUNTID = [Source].AccountID 
WHEN NOT MATCHED BY Target THEN
INSERT (ACCOUNTID,CREATEDATE,INDUSTRYPRACTICE) VALUES ([Source].Accountid,getdate(),'TEST');

But a more preferable approach would be to avoid using reserved words as aliases like shown below:


MERGE 
[SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100] AS TGT
USING 
[SALESLOGIX].[sysdba].[ACCOUNT] AS SRC
ON 
TGT.ACCOUNTID = SRC.AccountID 
WHEN NOT MATCHED BY TARGET THEN
INSERT (ACCOUNTID,CREATEDATE,INDUSTRYPRACTICE) VALUES (SRC.Accountid,getdate(),'TEST')
;
go

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-07-10 : 14:20:31

James thank you for the code refresher on reserved words. Little things escape the brain.
I pasted your code above using the 2nd set and am still getting the error.

Bryan Holmstrom

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-10 : 14:53:27

Which version of SQL you have? The code looks good to me at first look, but after james suggestion and your response of still getting the error, I performed following test and it worked

create table #C_ACCTS_MARKETING_100 (ACCOUNTID int)
create table #ACCOUNT (ACCOUNTID int)
insert into #C_ACCTS_MARKETING_100 values (1)
insert into #ACCOUNT values (2)
MERGE #C_ACCTS_MARKETING_100 AS Target
USING #ACCOUNT AS Source ON Target.ACCOUNTID = Source.AccountID
WHEN NOT MATCHED BY TARGET THEN
INSERT (ACCOUNTID) VALUES (Source.Accountid)
;
go
select * from #C_ACCTS_MARKETING_100

Cheers
MIK

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-07-10 : 15:15:10

I am using SQL SERVER 2008 R2

Bryan Holmstrom

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-07-10 : 15:18:18

SQL 2008 R2

I just tried your code and got these errors:

Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'MERGE'.
Msg 208, Level 16, State 0, Line 1
Invalid object name '#C_ACCTS_MARKETING_100'.

Bryan Holmstrom

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-10 : 15:38:01

I think the server you are connected to is SQL 2005. You client tools may be SQL 2008, but the server itself is 2005. You can verify by running this query: