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
 General SQL Server Forums
 New to SQL Server Programming
 SQL 2008 Merge
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

412 Posts

Posted - 04/23/2013 :  09:25:45  Show Profile  Reply with Quote
I try to insert a new record into table B but it seems like it doesn't insert the record. It only update. Anythg wrong with my query?

MERGE dbo.tableA AS B
USING [...].[...].[...].tableB AS A with (nolock)

ON A.col1=B.col1
AND A.col2=B.col2

WHEN MATCHED THEN

UPDATE
SET B.col1= A.col1,
B.col2=A.col2,
B.col3=A.col3

WHEN NOT MATCHED THEN

INSERT(col1,col2,col3)
VALUES(A.col1,A.col2,A.col3);

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/23/2013 :  10:15:46  Show Profile  Reply with Quote
well it is confusing to look at because you aliased tableA as B and tableB as A.
So your update is updating TableA (B) but your insert is inserting columns from TableB (A) from TableB (itself).

Be One with the Optimizer
TG
Go to Top of Page

peace
Constraint Violating Yak Guru

412 Posts

Posted - 04/23/2013 :  10:28:38  Show Profile  Reply with Quote
what should be the correct query
Go to Top of Page

peace
Constraint Violating Yak Guru

412 Posts

Posted - 04/23/2013 :  10:47:39  Show Profile  Reply with Quote
i get this error:

The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3741 Posts

Posted - 04/23/2013 :  10:55:21  Show Profile  Reply with Quote
quote:
Originally posted by peace

i get this error:

The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables.

It is just one of those restrictions on MERGE statement http://technet.microsoft.com/en-us/library/bb510625.aspx

So you will have to do it the old fashioned way as two separate statements.
Go to Top of Page

peace
Constraint Violating Yak Guru

412 Posts

Posted - 04/23/2013 :  11:02:10  Show Profile  Reply with Quote
i tried to insert and update in separate statements.

but it seems like it insert the whole record.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/23/2013 :  13:45:53  Show Profile  Reply with Quote
I think you're going to need to post the actual code for us to help you. Also post the specific problem when you run the code (you will post). If it is an error then post the exact error.

Be One with the Optimizer
TG
Go to Top of Page

peace
Constraint Violating Yak Guru

412 Posts

Posted - 04/23/2013 :  22:00:32  Show Profile  Reply with Quote
i tried this but i get this error:

The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables.

merge to linked server which is tableA

MERGE [...].[...].[...].tableA AS B
USING tableB AS A with (nolock)

ON A.col1=B.col1
AND A.col2=B.col2

WHEN MATCHED THEN

UPDATE
SET B.col1= A.col1,
B.col2=A.col2,
B.col3=A.col3

WHEN NOT MATCHED THEN

INSERT(col1,col2,col3)
VALUES(A.col1,A.col2,A.col3);
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/23/2013 :  23:24:58  Show Profile  Reply with Quote
what is this?
quote:
[...].[...].[...].

did you just remove the [<server>].[<database>].[<schema>]. names so we couldn't see them or is that your actual code?
In either case, as the error message and James both say, you can't perform a merge across two database servers.

"remote" means a table on a different server. Is that what you're trying to do?
The best practice method for keeping a remote table in sync with a local table is with sql replication.



Be One with the Optimizer
TG
Go to Top of Page

peace
Constraint Violating Yak Guru

412 Posts

Posted - 04/24/2013 :  00:47:35  Show Profile  Reply with Quote
It is linked server.
Now im getting it correct already.

Just add in

exec ('MERGE STATEMENT') at LINKSERVER

Now i try to update new record.
But it seems like it insert instead of doing the update.
My merge query still maintain the same as above.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 04/24/2013 :  01:26:46  Show Profile  Reply with Quote
Be sure it satisfies the condition (A.col1=B.col1 AND A.col2=B.col2)...


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/24/2013 :  01:33:27  Show Profile  Reply with Quote
quote:
Originally posted by peace

It is linked server.
Now im getting it correct already.

Just add in

exec ('MERGE STATEMENT') at LINKSERVER

Now i try to update new record.
But it seems like it insert instead of doing the update.
My merge query still maintain the same as above.


then the issue might be in columns you're defined for checking. are you sure you've included correct set of columns which will uniquely identify a new row (pk combination)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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