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

SQL Server 2008 Forums

Transact-SQL (2008)

MERGE

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-06-24 : 17:28:58

Hello I use this query to update

merge hospital using (
select
PRVDR_NUM,
ORG_NPI_NUM
FROM overlap
)
AS OVR (PR, NPI)
on (hospital.Ho_num = OVR.PR)
when matched then update set hospital.HO_NPI = OVR.NPI;

but it gives me a error

Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'merge'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'AS'.

table hospital: ho_num (PK) char, ho_npi are char
overlap: PRVDR_NUM,ORG_NPI_NUM are varchar

any reason why i get this error?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-24 : 18:58:31

1) Previous statment must be terminated by a semicolon
2) Compatibilitty level must be set to 100 or more

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-24 : 19:00:31

[code];MERGE dbo.Hospital AS tgt
USING (
SELECT PRVDR_NUM,
ORG_NPI_NUM
FROM dbo.Overlap
) AS src(PR, NPI) ON src.PR = tgt.Ho_num
WHEN MATCHED
THEN UPDATE
SET tgt.HO_NPI = src.NPI;[/code]

N 56°04'39.26"
E 12°55'05.63"

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-06-25 : 09:40:22

hello
i used this to change my compatibility level

ALTER DATABASE BRSDATA
SET compatibility_level = 100;
go

the current compatibility level is 80

i am working on SSMS 2008

and when i run the Alter command above i get

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

any help
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-25 : 11:48:51

Well, even if you work with SSMS 2008, your database may still be SQL Server 2000.
What does SELECT @@VERSION return for you?

N 56°04'39.26"
E 12°55'05.63"

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-06-25 : 13:55:42

Microsoft SQL Server 2000 - 8.00.2055 (Intel X86) Dec 16 2008 19:46:53 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

it says 2000, so the alter database command to change compatibility level should work, rite?
thnx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-26 : 05:06:41

quote:
Originally posted by jayram11

Microsoft SQL Server 2000 - 8.00.2055 (Intel X86) Dec 16 2008 19:46:53 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

it says 2000, so the alter database command to change compatibility level should work, rite?
thnx

Nope. SQL 2000 is basically 80. 100 is SQL 2008. For SQL 2000, you can't change the compatibility level higher than 80.

Anyway, since you are using SQL 2000, you should be posting your question under a SQL 2000 forum. And MERGE is not available under 2000. Where did you get that syntax from ?

KH
[spoiler]Time is always against us[/spoiler]

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-06-28 : 09:03:49

i am using SQL server 2008 and the database was created under 2000 and i want to change the compatibility level to 100 for merge statement to work
Thnks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-28 : 09:12:06

If you are using SSMS 2008 and querying a SQL Server 2008, this statement should work

ALTER DATABASE BRSDATA
SET compatibility_level = 100;

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-06-28 : 11:24:42

But you shouldn't change compatibility level without retesting the whole application!

If its just for DEV or some experiment then fine. If it is for DEV and you are going to deploy the changes to Production then you'll need a complete regression test. Or cross-your-fingers-and-hope.

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-06-28 : 15:56:58

so i am not going to use the merge statement rather use the update statement to update the records based on join.....wanted to use the Merge anywayssss

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources