Sql Merge - 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

General SQL Server Forums

New to SQL Server Programming

Sql Merge

Author

Topic

willware
Starting Member

6 Posts

Posted - 2011-08-22 : 22:43:59

i am trying to insert or update a table from another table here is my script with the error.

USE DEMS;
GO
BEGIN TRAN;
MERGE DEMS_acct AS T
USING DemAcct AS S
ON (T.acct_ID = S.ACCT1 AND T.acctsetcode = S.AcSetId1)
WHEN NOT MATCHED BY DEMS_acct
THEN INSERT(site_ID, acct_ID, subid, subsubid, acctsetcode) VALUES(S.Sitid1, S.ACCT1, S.SubID1, S.SuSuID1, S.AcSetId1)
WHEN MATCHED
THEN UPDATE SET T.site_ID = S.Sitid1, T.acct_ID = S.ACCT1, T.subid = S.SubID1, T.subsubid = S.SuSuID1, T.acctsetcode = S.AcSetId1

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'MERGE'.

William E Ware

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2011-08-22 : 22:47:41

Which version of sql server you are using?

Harsh Athalye
http://www.letsgeek.net/

willware
Starting Member

6 Posts

Posted - 2011-08-22 : 22:54:47

2008

William E Ware

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2011-08-22 : 23:54:57

How about this?


USE DEMS
GO

BEGIN TRAN

MERGE DEMS_acct AS T
USING DemAcct AS S
ON (T.acct_ID = S.ACCT1 AND T.acctsetcode = S.AcSetId1)
WHEN MATCHED
THEN UPDATE SET T.site_ID = S.Sitid1, T.acct_ID = S.ACCT1, T.subid = S.SubID1, T.subsubid = S.SuSuID1, T.acctsetcode = S.AcSetId1
WHEN NOT MATCHED BY TARGET
THEN INSERT(site_ID, acct_ID, subid, subsubid, acctsetcode) VALUES(S.Sitid1, S.ACCT1, S.SubID1, S.SuSuID1, S.AcSetId1)

Harsh Athalye
http://www.letsgeek.net/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 00:16:31

and MERGE should terminate with a ; character

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

willware
Starting Member

6 Posts

Posted - 2011-08-23 : 07:01:09

USE DEMS
GO
BEGIN TRAN;
MERGE DEMS_acct AS T
USING DemAcct AS S
ON (T.acct_ID = S.ACCT1 AND T.acctsetcode = S.AcSetId1)
WHEN MATCHED
THEN UPDATE SET T.site_ID = S.Sitid1, T.acct_ID = S.ACCT1, T.subid = S.SubID1, T.subsubid = S.SuSuID1, T.acctsetcode = S.AcSetId1
WHEN NOT MATCHED BY TARGET
THEN INSERT(site_ID, acct_ID, subid, subsubid, acctsetcode) VALUES(S.Sitid1, S.ACCT1, S.SubID1, S.SuSuID1, S.AcSetId1);

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'MERGE'.

William E Ware

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 07:17:48

are you using sql 2008? what does below return?

SELECT @@VERSION
GO
EXEC sp_dbcmptlevel 'your db name'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

willware
Starting Member

6 Posts

Posted - 2011-08-23 : 07:38:56

Sorry it is 2005
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 6.0 (Build 6001: Service Pack 1)

William E Ware

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 07:43:32

MERGE is available only from SQL 2008 onwards
if its sql 2005 you should use UPDATE/INSERT combination to implement above

it should be like


 
INSERT DEMS_acct 
(site_ID, acct_ID, subid, subsubid, acctsetcode) 
SELECT S.Sitid1, S.ACCT1, S.SubID1, S.SuSuID1, S.AcSetId1
FROM DemAcct S 
LEFT JOIN DEMS_acct T
ON T.acct_ID = S.ACCT1 
AND T.acctsetcode = S.AcSetId1
WHERE T.acct_ID IS NULL

UPDATE T
SET T.site_ID = S.Sitid1, 
T.acct_ID = S.ACCT1, 
T.subid = S.SubID1, 
T.subsubid = S.SuSuID1, 
T.acctsetcode = S.AcSetId1
FROM DemAcct S 
INNER JOIN DEMS_acct T
ON T.acct_ID = S.ACCT1 
AND T.acctsetcode = S.AcSetId1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

willware
Starting Member

6 Posts

Posted - 2011-08-23 : 08:27:58

Thank you

William E Ware

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 09:22:20

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources