MERGE t-sql command

I am trying to use the following sql command to merge "membership" data from a view to a table.

I can't get it to work. I keep getting the following error message:


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'USING'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'MembershipTempData'.

any ideas?
MERGE snapshot_entity_membership USING 
(
SELECT
entity_id, area_of_giving_code, membership_category_code, membership_category_desc, membership_type_code, membership_type_desc, 
                      membership_status_code, membership_status_desc, expiration_date, add_date, add_operator, change_date, change_operator, snapshot_date 
FROM snapshot_entity_membership_view) MembershipTempData
ON (snapshot_entity_membership.entity_id = MembershipTempData.entity_id)
WHEN NOT MATCHED BY TARGET THEN
    INSERT (entity_id, area_of_giving_code, membership_category_code, membership_category_desc, membership_type_code, membership_type_desc, 
                      membership_status_code, membership_status_desc, expiration_date, add_date, add_operator, change_date, change_operator, snapshot_date)
    VALUES (MembershipTempData.entity_id, MembershipTempData.area_of_giving_code, MembershipTempData.membership_category_code, MembershipTempData.membership_category_desc, MembershipTempData.membership_type_code, MembershipTempData.membership_type_desc, MembershipTempData.membership_status_code, MembershipTempData.membership_status_desc, MembershipTempData.expiration_date, MembershipTempData.add_date, MembershipTempData.add_operator, MembershipTempData.change_date, MembershipTempData.change_operator, MembershipTempData.snapshot_date)

Open in new window

IUAATechAsked:
Who is Participating?
 
cyberkiwiCommented:
Run this - exec sp_dbcmptlevel 'tempdb'  -- replace with your db name

If it doesn’t return this, it won’t work - The current compatibility level is 100.
0
 
cyberkiwiCommented:
The statement looks fine and syntax checks.

It would appear you are not using SQL Server 2008 or R2, or the database compability level is less than 100.
0
 
IUAATechAuthor Commented:
you are right. The compatibility level is 90. I will check with our DBA.

thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.