January 10, 2012 at 9:57 am
Hi All
I am new to merge statement so need your help in making it better understanding for my task.
I have set of data in a table1 and set of data in table2 i need to compare the date from table1 to table2 if record exists then need to update with new value from table2 to table1 and if record does not exists then need to insert the fresh one from table2. can any one help me out how to achive this task.
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
January 10, 2012 at 12:25 pm
sami.sqldba (1/10/2012)
Hi AllI am new to merge statement so need your help in making it better understanding for my task.
I have set of data in a table1 and set of data in table2 i need to compare the date from table1 to table2 if record exists then need to update with new value from table2 to table1 and if record does not exists then need to insert the fresh one from table2. can any one help me out how to achive this task.
Here's my understanding of the merge statement. IT COULD BE COMPLETELY WRONG, so as always when taking code from strangers, take this stuff, understand it, verify it, and test it and understand it (then test it again). Obviously stuff inside brackets <> are to be replaced by your table and column names and key names and the brackets removed. Notice that in several places in this syntax, the source and destination table names are sort of implied by the first "arguments" of the merge syntax. Also there are plenty of more options in using the merge statement so the best way to learn it is inside your SSMS window with some test tables on a test database. No substitute for testing.
Did I forget to tell you to test your code? Also, don't forget to test your code.
MERGE <table1>
USING <table2>
ON <table1.date_key> = <table2.date_key>
WHEN MATCHED THEN
UPDATE SET <table1.column_destination> = <table2.column_source>
WHEN NOT MATCHED THEN
INSERT (<column_list>)
VALUES (<table2.column_list>)
For example, here's my test code. You should test it yourself. Notice that when I have test data, I included in the test data text the table it was originally inserted into, thus I can verify the results in this test set.
CREATE TABLE [dbo].[TABLE1](
[KEY1] [varchar](20) NULL,
[DATA1] [varchar](20) NULL
)
CREATE TABLE [dbo].[TABLE2](
[KEY2] [varchar](20) NULL,
[DATA2] [varchar](20) NULL
)
INSERT INTO TABLE1
SELECT * FROM
(
SELECT 'KEY1' KEY1,'TABLE 1 DATA 1' DATA1 UNION
SELECT 'KEY2' KEY1,'TABLE 1 DATA 2' DATA1 UNION
SELECT 'KEY3' KEY1,'TABLE 1 DATA 3' DATA1 UNION
SELECT 'KEY4' KEY1,'TABLE 1 DATA 4' DATA1
) DATA1
INSERT INTO TABLE2
SELECT * FROM
(
SELECT 'KEY1' KEY2,'TABLE 2 DATA 1' DATA2 UNION
SELECT 'KEY2' KEY2,'TABLE 2 DATA 2' DATA2 UNION
SELECT 'KEY3' KEY2,'TABLE 2 DATA 3' DATA2 UNION
SELECT 'KEY4A' KEY2,'TABLE 2 DATA NOMATCH' DATA2
) DATA2
MERGE TABLE1
USING TABLE2
ON TABLE1.KEY1 = TABLE2.KEY2
WHEN MATCHED THEN
UPDATE
SET DATA1 = DATA2
WHEN NOT MATCHED THEN
INSERT (KEY1, DATA1)
VALUES (KEY2, DATA2);
SELECT * FROM TABLE1
Also, don't forget to fully test your code before running it on a production database. Additionally you'll need to test your code before running it on a production database.
Oops, did I mention testing?
January 10, 2012 at 12:28 pm
sami.sqldba (1/10/2012)
I am new to merge statement so need your help in making it better understanding for my task
In addition to Patrick's example, take a look at the other questions regarding MERGE on the same page of the forum you posted to. There are currently several very good examples to look at.
Also see:
Optimizing Merge Statement Performance on TechNet. The title does not do it justice - it is an excellent item to read to discover the common mistakes made when using MERGE. Don't forget to test your code.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 11, 2012 at 2:57 am
Hi
hey thanks to both yes indeed need to understand the things before taking help and cross checking the good lesson will surely follow in future..
Thanks guys for your support and helping nature...
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply