February 7, 2011 at 12:13 pm
I have a source table A in one database DB1 and destination table B in another database DB2 in the same server.
Bot the table are almost same except the dest table B has 2 extra columns that I don't need to populate.
I need to check if table B has any IDs from table A that are not in B . Then insert . Else if there update all the fields that need to be updated. (Other fields include Location,city,Phone etc)
Ex
TaBLE A
ID Name Location City Phone
1 ABC Street NoCity 222-222-3333
2 CDE SAM street FFF 111-111-1111
Table B
ID Name Location City Phone
1 ABC Street YesCity 333-333-3333
Then Table B should be
ID Name Location City Phone
1 ABC Street NoCity 222-222-3333
2 CDE SAM street FFF 111-111-1111
Please help.
February 7, 2011 at 12:14 pm
You're in SQL 2k8... hit up BOL and review the MERGE command.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 7, 2011 at 3:56 pm
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
February 7, 2011 at 4:01 pm
MERGE DB1.dbo.TEST1 AS TEST1
USING db2.dbo.test2 AS test2
ON TEST1.columA= test2.columA
WHEN MATCHED THEN
UPDATE SET test1.Processor = test2.Processor,
test1.Location = test2.Location,
WHEN NOT MATCHED THEN
INSERT (InternalID,
Processor,
ATM_Location)
VALUES(test2.ATMInternalID,
test2.Processor,
test2.Location) ;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply