SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Merge Statement Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
75 Posts

Posted - 07/10/2013 :  14:00:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 07/10/2013 :  14:15:30  Show Profile  Reply with Quote
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

Edited by - James K on 07/10/2013 14:15:46
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
75 Posts

Posted - 07/10/2013 :  14:20:31  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 07/10/2013 :  14:53:27  Show Profile  Reply with Quote
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
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
75 Posts

Posted - 07/10/2013 :  15:15:10  Show Profile  Reply with Quote
I am using SQL SERVER 2008 R2

Bryan Holmstrom
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
75 Posts

Posted - 07/10/2013 :  15:18:18  Show Profile  Reply with Quote
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

Edited by - bholmstrom on 07/10/2013 15:26:49
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 07/10/2013 :  15:38:01  Show Profile  Reply with Quote
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:
SELECT @@version
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
75 Posts

Posted - 07/10/2013 :  15:55:46  Show Profile  Reply with Quote
Wow....SQL Server 2000

Bryan Holmstrom
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 07/10/2013 :  16:16:57  Show Profile  Reply with Quote
That would explain it :)

With SQL 2000 you will have to use use a combination of updates, deletes and inserts.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/10/2013 :  17:15:09  Show Profile  Reply with Quote
quote:
Originally posted by James K

Target and Source are keywords that MERGE uses, so you should escape them like shown below
FYI, That is not true. Source and Target are not reserved words.

EDIT: Cut off part of the quote.

Edited by - Lamprey on 07/10/2013 17:20:34
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 07/10/2013 :  17:36:01  Show Profile  Reply with Quote
You are right. That was a guess based on the fact that merge syntax uses words like "WHEN NOT BY TARGET".
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000