by

SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.

One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

Syntax of MERGE statement is as following:
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]
;
Example:

Let’s create Student Details and StudentTotalMarks and inserted some records.

Student Details:

USE AdventureWorks
GO
CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15)
)
GO
INSERT INTO StudentDetails
VALUES(1,'SMITH')
INSERT INTO StudentDetails
VALUES(2,'ALLEN')
INSERT INTO StudentDetails
VALUES(3,'JONES')
INSERT INTO StudentDetails
VALUES(4,'MARTIN')
INSERT INTO StudentDetails
VALUES(5,'JAMES')
GO

StudentTotalMarks:

CREATE TABLE StudentTotalMarks
(
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)
GO
INSERT INTO StudentTotalMarks
VALUES(1,230)
INSERT INTO StudentTotalMarks
VALUES(2,255)
INSERT INTO StudentTotalMarks
VALUES(3,200)
GO

In our example we will consider three main conditions while we merge this two tables.

  1. Delete the records whose marks are more than 250.
  2. Update marks and add 25 to each as internals if records exist.
  3. Insert the records if record does not exists.

Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.

MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
GO

There are two very important points to remember while using MERGE statement.

  • Semicolon is mandatory after the merge statement.
  • When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.

After the MERGE statement has been executed, we should compare previous resultset and new resultset to verify if our three conditions are carried out.

AS we can see there are 5 rows updated. StudentID 2 is deleted as it is more than 250, 25 marks have been added to all records that exists i.e StudentID 1,3 and the records that did not exists i.e. 4 and 5 are now inserted in StudentTotalMarks .

MERGE statement is very handy improvement for T-SQL developers who have to update database tables with complicated logic. MERGE statement also improves the performance of database as it passes through data only once.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

108 thoughts on “SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE”

  1. Nice article…I have one doubt…

    Do we need to select StudentName in the Source Table like
    “SELECT StudentID,StudentName FROM StudentDetails”, which is never used in the Join?

    Reply

  3. What if we want a conditional merge or insert

    Eg: if(condition = true)
    (
    insert into table1 values(select * from tableE) where ..
    )
    else
    (

    )

    how does such things work, can u help me.

    Thanks

    Reply

  6. Hi Pinal Dave,
    Thanks for most of the very useful articles you write..Just something that still puzzles me and you might have the answer.
    To be it seems 2008 MERGE statement and CDC perform the same function during the ETL process that is best used in SCD situations.
    What i do see is CDC tracking and capturing data into a Different Table..
    My question is do they perform different functions.

    Thanks

    Reply

  7. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 6 Journey to SQL Authority with Pinal Dave

  8. How to merge 2 tables … eg : Table1 and Table2 , these two tables should be merged into Table3 .. means creating this new table3 contaning the data of table1 and table2

    Reply

    • You cannot create a table using MERGE. We can move the data from tables into one table.

      MERGE TABLE3 as T3
      USING ( select T1.*,T2.* from T1,T2 where join condition of T1 and T2) T4
      ON (Join condition of T3 and Source tables)
      WHEN NOT MATCHED THEN
      INSERT (Column List)
      VALUES( );

      Reply

  10. thenqq Imran :)

    one more doubt ,is it possbile to combine the 2 tables into a new table .. The join keyword is only selecting the information.

    Reply

  11. Can you cancel or stop a MERGE statement ? What if I need to Merge 2 Huge tables and then for some reason, I would like to cancel the curent operation. Is this possible ? Can I Rollback ? Thanks

    Reply

  12. Hi Pinal Dave,
    really great information!!
    please tell me how to merge data into two table
    like newtable has data for table1 and table2
    table1 and table2 has matching columns

    so how i’ll fetch table1′s new indent value to insert record for it into table2
    –
    thanks in advance

    Reply

    • –Create a target table
      CREATE TABLE Products
      (
      ProductID INT PRIMARY KEY,
      ProductName VARCHAR(100),
      Rate MONEY
      )
      GO
      –Insert records into target table
      INSERT INTO Products
      VALUES
      (1, ‘Tea’, 10.00),
      (2, ‘Coffee’, 20.00),
      (3, ‘Muffin’, 30.00),
      (4, ‘Biscuit’, 40.00)
      GO
      –Create source table
      CREATE TABLE UpdatedProducts
      (
      ProductID INT PRIMARY KEY,
      ProductName VARCHAR(100),
      Rate MONEY
      )
      GO
      –Insert records into source table
      INSERT INTO UpdatedProducts
      VALUES
      (1, ‘Tea’, 10.00),
      (2, ‘Coffee’, 25.00),
      (3, ‘Muffin’, 35.00),
      (5, ‘Pizza’, 60.00)
      GO
      SELECT * FROM Products
      SELECT * FROM UpdatedProducts
      GO

      Next I will use the MERGE SQL command to synchronize the target table with the refreshed data coming from the source table.

      MERGE SQL statement – Part 2

      –Synchronize the target table with
      –refreshed data from source table
      MERGE Products AS TARGET
      USING UpdatedProducts AS SOURCE
      ON (TARGET.ProductID = SOURCE.ProductID)
      –When records are matched, update
      –the records if there is any change
      WHEN MATCHED AND TARGET.ProductName SOURCE.ProductName
      OR TARGET.Rate SOURCE.Rate THEN
      UPDATE SET TARGET.ProductName = SOURCE.ProductName,
      TARGET.Rate = SOURCE.Rate
      –When no records are matched, insert
      –the incoming records from source
      –table to target table
      WHEN NOT MATCHED BY TARGET THEN
      INSERT (ProductID, ProductName, Rate)
      VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
      –When there is a row that exists in target table and
      –same record does not exist in source table
      –then delete this record from target table
      WHEN NOT MATCHED BY SOURCE THEN
      DELETE
      –$action specifies a column of type nvarchar(10)
      –in the OUTPUT clause that returns one of three
      –values for each row: ‘INSERT’, ‘UPDATE’, or ‘DELETE’,
      –according to the action that was performed on that row
      OUTPUT $action,
      DELETED.ProductID AS TargetProductID,
      DELETED.ProductName AS TargetProductName,
      DELETED.Rate AS TargetRate,
      INSERTED.ProductID AS SourceProductID,
      INSERTED.ProductName AS SourceProductName,
      INSERTED.Rate AS SourceRate;
      SELECT @@ROWCOUNT;
      GO

      this code works..try..

      [email removed]

      Reply

      • Hi Rakshit,
        The above code doesn’t work.
        Because in NOT MATCHED condition we cannot perform the DELETE operation.I tried the same.
        If am wrong correct me.

        Thanks

        Reply

  14. Hi pinal,

    I want to update or insert into more than one tables so can we use multiple merge statement in one StoredProcedure itself

    Reply

  15. Hello Divya,

    There is no maximum number limitation on merge statements in a stored procedure. Are you facing any issue in writing multiple merge statements in a stored procedure?

    Regards,
    Pinal Dave

    Reply

  16. An action of type ‘WHEN MATCHED’ cannot appear more than once in a ‘UPDATE’ clause of a MERGE statement.
    i m getting this error when i m trying to use matched condition for update more than once so how i can handle this issue…
    please guide me soon as possible…
    i am really in urgency to complete this task

    Reply

  18. Hello,

    My need is if the rows are not macthing I need to insert them but one at a time as I have to get the max(id) + 1 for each insert to be able to populate it.

    How can I insert one statement at a time just after I get select @traceid = max(id)+1

    insert into abc (@traceid, ‘test’)

    Reply

  19. Hi, Dave

    I Need to act this Merge as REPLACE behavior in MySQL
    as far i use like this query to implement it

    MERGE INTO StudentDetails as a
    USING (SELECT ’4′ as StudentId) as b
    ON a.StudentId = b.StudentId
    WHEN MATCHED THEN
    UPDATE SET StudentName = StudentName+’ UPDATED’
    WHEN NOT MATCHED THEN
    INSERT (StudentId, StudentName) VALUES (’4′, ‘Martin Inserted’);

    previously on SQL 2005 I Use IF EXISTS and DELETE then insert

    Reply

  20. What about the old fashioned left join for upsert?

    UPDATE tbl_tempManifest AS tm
    LEFT JOIN tbl_Manifest AS m ON tm.tracking=m.tracking
    SET m.name = tm.name,
    m.store = tm.store,
    m.orderNum = tm.orderNum,
    m.tracking = tm.tracking;

    This update’s or inserts, would I benefit from using MERGE in this situation?

    Reply

  21. Sothing strange, i have sql 2008
    when matched => update is ok
    WHEN NOT MATCHED => not run at all

    —————————————-

    merge into [import].[CityImport] target

    using
    ( SELECT CityId ,CityName FROM [import].[CityImport] WHERE CityId=@CityId ) source
    on
    (
    target.CityId=source.CityId
    )
    when matched then
    UPDATE
    SET Target.[CityName] = @CityName

    WHEN NOT MATCHED THEN
    INSERT
    (CityId ,CityName )
    VALUES
    (1333331,
    ’111′) ;
    END

    Reply

  22. How we can do same type of merging in SQL server 2005?

    for eg:

    GroupId player
    ————————–
    1 P-1
    2 P-2
    3 P-3
    1 P-11
    2 P-21
    1 P-12

    Result should be like this:

    GroupId player
    ————————–
    1 P-1,P-11,P-12
    2 P-2,P-21
    3 P-3

    Please provide the solution as my requirements

    Thanks in advance

    Reply

    • SELECT DISTINCT t1.GroupId,
      STUFF((SELECT DISTINCT TOP 100 PERCENT ‘,’ + t2.player FROM table AS t2 WHERE t2.GroupId = t1.GroupId ORDER BY ‘,’ + t2.player FOR XML PATH(”)), 1, 1, ”) AS player
      FROM table AS t1
      ORDER BY t1.GroupId

      Reply

      • SQL STUFF is Very nice stuff. I do have question about your query. Because I want to understand your query. why did you use DISTINCT in sub query? This query result is same without DISTINCT in sub query.

        I mentioned this place DISTINCT not main query DISITINCT :-

        SELECT DISTINCT TOP 100 PERCENT ‘,’ + t2.player FROM table AS t2 WHERE t2.GroupId = t1.GroupId ORDER BY ‘,’ + t2.player FOR XML PATH(”)

        Please reply if any specific reason

        Reply

  23. Thanks Madhivanan,

    Your query is working as my expectation. Can you provide some link which is new in sql server 2005?

    Reply

  24. I’m attempting to write a merge statement and have the original merge table be dynamic so it can run for multiple tables using a for loop. However, it seems I cannot have the table name for the merge be a variable. . .is this not possible? Example:
    MERGE @DestinationTableName AS Test
    Using( etc. . .

    Reply

  26. I have Employee and Emplyee History. If any employee is inserted then I have to create a new record in History with the Inserted Id. I think we cannot write in When Not Matched Clause because it allows only one Insert statement

    Reply

    • Hi, Firstly thanks for the article seems that it’s going to save heaps of times/code fro me, but i have similar issue as Rajkumar does, One of target’s field is a foreign key from another table which I have to insert into that one first, Any idea , tips or tricks or here I missing something?

      Reply

  30. MERGE StudentTotalMarks AS stm USING ( SELECT StudentID,
    StudentName
    FROM StudentDetails
    ) AS sd
    ON stm.StudentID = sd.StudentID WHEN MATCHED AND stm.StudentMarks > 250
    THEN DELETE WHEN MATCHED THEN UPDATE
    SET stm.StudentMarks = stm.StudentMarks + 25 WHEN NOT MATCHED THEN
    INSERT ( StudentID, StudentMarks )
    VALUES
    ( sd.StudentID, 25 ) ;

    This Code Generate Error…

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘AS’.
    Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword ‘AS’.

    Reply

  32. I have Implemented merge for pulling data fro my source system where i pull around 58000 records.

    First time the MERGE Statement took around 1 hour 15 mins.

    I have a condition in my MERGE statement after the ON condtions checking lastmodified_date_onserver > max(lastmodifieddate at my server)

    Ideally i was expecting this to run on a subset of data dn give me faster response. It doesn’t. it is still taking same one hour time.

    Any suggestion on where to place the condtion and what can i do to make the query more effective.

    thanks,
    Aditya

    Reply

  36. Hi Pinal.

    Interesting article. Here you are ‘the hero’…

    But I expose the next situation; I need merge some tables (for example table1 and table2) for two databases (BD1 as central and BD2 as extension) that have references between them, table1 is referenced for table2. If execute merge for table1, the data that exists in the table2 of BD2, in the reference value, not will be the same value after the merge. ¿how I do to merge the data in table2, if the reference value in table1 has changed and, I need do the insert with this value in the merge for table 2?

    Example.
    Table1: Customer
    IDCustomer = 1,
    NameCustome = JOHN.

    Table2: CustomerPhone
    IDCustomerPhone = 1,
    IdCustomer = 1,
    NumberPhone = 1234567890

    IDCustomerPhone = 2,
    IdCustomer = 1,
    NumberPhone = 0987654321

    For example the IDCustomer has change in the merge to 5, and this value is necesary for the merge in the table2. ¿How I do for make this, without problems?

    Thanks in advance,

    Reply

  41. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 15 of 31 Journey to SQLAuthority

  45. Can we have multiple source tables?
    ex:

    merge targettable t
    using (select s1.col1, s2.col2 from source1 s1 join source2 s2
    on s1.col3 = s2.col3 ) sourcetable s3
    when matched
    then update set t.col1 = s3.col1,
    t.col2 = s3.col2 ;

    instead above query if write like

    merge targettable t
    using
    source1 s1
    join source2 s2
    on s1.col3 = s2.col3
    when matched
    then update set t.col1 = s1.col1,
    t.col2 = s2.col2 ;

    Reply

  46. Hi,

    I have 2 tables which has only one column and contains mobile numbers. I want to combine both the tables and want to make 1 table and want to remove duplicate entries after that. Pls suggest the easiest way to perform the same.I am using SSMS 2008 version.

    Regards,
    Amit.

    Reply

  49. I was excited to use this feature and replace this wiht my legacy insert/update statment i was writting for SSIS package to sync my data however the exececution plan show that it is not performance effective and it was using 87% of the time comparet to my normal insert/update. has any one else tested the performance of the query.

    Reply

  52. getting below error even i am updating single column that too unique records

    Msg 8672, Level 16, State 1, Procedure R_verificationData_BatchJobs, Line 5
    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.

    Reply

  53. Sir table1 is my base table i want to reverse your query i am Trying

    MERGE table1 AS m
    USING (SELECT ServiceAreaCode,PhoneNumbers,Preferences,Opstype,PhoneType FROM table2) AS m1
    ON m1.PhoneNumbers = m.PhoneNumbers
    WHEN MATCHED AND m.Opstype = ‘”a”‘ THEN DELETE;

    but it give errore
    Msg 547, Level 16, State 0, Line 1
    The MERGE statement conflicted with the REFERENCE constraint “FK__manoj2__PhoneNum__1273C1CD”. The conflict occurred in database “test”, table “dbo.manoj1″, column ‘PhoneNumbers’.
    The statement has been terminated.

    Please Help me

    Reply

  56. Pingback: SQL SERVER – MERGE or INSERT, UPDATE, DELETE – Quiz – Puzzle – 19 of 31 « SQL Server Journey with SQL Authority

  57. Hi sir,

    Delete Duplicates And Resolve Foreign Key References using merge?
    If possible for set multiple source tables or target tables?please send examples..

    Reply

  58. hi Pinal,

    Is it possible to store the output of the MERGE statement in a history table to refer at a later stage… by outpput i mean the changes that occured as part of the merge.

    Reply

  59. One thing that is documented very well is that subselects are NOT allowed in the INSERT or UPDATE statements. But they are allowed in the WHEN (NOT) MATCHED clause.

    I have found that instead of putting the subselect in the INSERT or UPDATE to place it as a join in the Source table for later use.

    – (For example this will NOT work): —>
    MERGE users AS Target
    USING (
    SELECT id, name
    FROM locations
    ) AS Source
    ON (Target.id = Source.id)

    WHEN NOT MATCHED BY Target
    THEN INSERT(name, parent_id, status)
    VALUES ( name, (SELECT id FROM users WHERE pa_id = Source.id), 1 )

    WHEN NOT MATCHED BY Source AND Target.id IN (SELECT id FROM users
    WHERE pa_id IN (1,2))
    THEN UPDATE SET status = 0 –deactivate

    WHEN MATCHED
    THEN UPDATE SET status = 1, pa_id = (SELECT id FROM users WHERE pa_id = Source.id) –reactivate and update parent id.
    ;
    ———————————————————–
    – (Instead it needs to look more like this): —>
    MERGE users AS Target
    USING (
    SELECT u.id, u.name, l.pa_id
    FROM locations l LEFT OUTER JOIN users u ON u.id = pa.id
    ) AS Source
    ON (Target.id = Source.id)

    WHEN NOT MATCHED BY Target
    THEN INSERT(name, parent_id, status)
    VALUES ( name, pa_id, 1 )

    WHEN NOT MATCHED BY Source AND Target.id IN (SELECT id FROM users
    WHERE pa_id IN (1,2))
    THEN UPDATE SET status = 0 –deactivate

    WHEN MATCHED
    THEN UPDATE SET status = 1, pa_id = pa_id –reactivate and update parent id.
    ;

    Reply

  60. Hi Pinal Dave

    I am working on Merge statement. Now I have a scenerio where I need to insert a record “when not matched” and also “when matched and Sourcecolumn1!=Targetcolumn1″.

    Could you please help me in this rregards?

    Thanks
    Swetha

    Reply

  61. INSERT INTO Products
    VALUES(1,’Tea’,10.00),(2,’Coffee’,20.00),(3,’Muffin’,30.00),(4,’Biscuit’,40.00)

    error: Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘,’.

    what the hell is this error

    Reply

  62. Hi Rakshith,

    Copy the below code and try.

    INSERT INTO Products
    VALUES
    (1,’Tea’,10.00),
    (2,’Coffee’,20.00),
    (3,’Muffin’,30.00),
    (4,’Biscuit’,40.00)

    Reply

  63. Rakshith,

    I copied your code and tried parsing ino SQL query. But there was an error as mentioned by you.

    I then replaced the single ” ‘ ” from the query by typing the same from key board. It worked fine.

    When I posted the comment and copied my code from this blog ( from my previous comment) and tried parsing . I got the same error.

    So, If you are copying and pasting the code, make sure to replace single apostropes by typing from the key board.

    Best Regards,
    Swetha

    Reply

  70. Your blog is very helpfull,

    One thing should include into article that we can not use alias name with column names in Insert statement :
    WHEN NOT MATCHED THEN
    INSERT(stm.StudentID,stm.StudentMarks)
    VALUES(sd.StudentID,25);

    and if we will do this we will get following error:
    “The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead.”

    Reply

  71. Hi,
    I have implemented SSIS package where i have used CDC feature to do insert/update/delete in table. can you please help me if we will go with Merge feature then will it make any difference in terms of performance.

    Reply

  72. Hi Pinal,

    I want to update a table which is not the source/target table in “when not matched” section using update query. is that possible?

    For example –

    MERGE Target AS T
    USING Source AS S
    ON (T.EmployeeID = S.EmployeeID)
    WHEN NOT MATCHED
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
    WHEN MATCHED
    THEN DELETE

    Reply

  74. My code:
    merge into #TEMP_table as Target
    using (
    select scol1, scol2, scol3, scol4 from test
    ) as Source
    on Target.tcol1=Source.scol1 and Target.tcol2=source.tcol2
    when matched then blah blah….

    but I keep getting that it cannot insert a row that already exists (my temp tables pk violated).
    When I search my source table, group by count there are none that are listed more than one time that are unique accross scol1 and scol2.

    select scol1, scol2, count(*) from test group by scol1, scol2 having count(*)>1 no rows returned

    Am I doing the on correctly to match against both columns?

    Reply

  75. This MERGE really awesome!
    Need to know one thing. I am using this to update data from excel files each month. Now if the user was in the database before but I get a new excel and he is not in there, I want to update the db record for him to inactive. Is there a way to do this?

    Reply

  76. @Derek, I am not PinalDave, but I didnt want to leave you hanging. There is an “when not matched by” option. So:

    merge into TargetTable as Target
    using (
    select scol1, scol2, scol3, scol4 from SourceTable
    ) as Source
    on Target.tcol1=Source.scol1
    when matched then
    blah blah….
    when not matched by Source then
    –this one is not found by source so do something
    when not matched by Target then
    –this one is not found by Target so do something

    Remeber to test all on a QA or Test system before implementing in a prod envoronment.

    Reply

  79. When I am trying to use under given syntax it is showing an error

    MERGE StudentTotalMarks AS stm
    USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
    ON stm.StudentID = sd.StudentID
    WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
    WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
    WHEN NOT MATCHED THEN
    INSERT(StudentID,StudentMarks)
    VALUES(sd.StudentID,25);

    ************************************

    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword ‘AS’.
    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword ‘AS’.

    Reply

  80. hi.I have only one table and column name is ID, Name ,Section, Amount i want to merge and sum the amount .update in one row and remove duplicate row..if in same there is same name and section..thanks in advance

    Reply

  81. Hello,
    I have a small problem with the merge statement, it involves updating when no changes are present.

    For example, Table A (TARGET) has 2 records, Table B (SOURCE) has 2 records. Table A has an auditing trigger set, anytime a record is updated, an audit table record is created. The tables have many fields and on update, there are many tables that are joined and the values are derived from other tables.

    The records in both tables are identical, so when I run the merge command, the ‘WHEN MATCHED THEN’ clause is hit, the records in the TARGET table are updated, and two new records are created in the audit table.

    The knee jerk reaction is to modify the trigger, but I would like to see if I can do this within the merge command.

    Thank you for your time,
    Edward Meshuris

    Reply

  82. hai, i have a setup where i need to store some data in 2 databases of SQL 2008 for redundant purpose. the SQL server is running in 2 different machines. both the machines collect the same data from same source (other server located in the same network) and store them. the problem i am facing is, if one server is switched OFF for say 1 hour, the server that was OFF, will not have the data of that period. when the server is back in service, the table will not be the same as in the database of second server.

    i want some procedure where we can check whether the 2 tables are different and if yes, merge the data such that both becomes same. structure of database, tables are exactly identical.

    any help in this is appreciated.

    Reply

  83. Hi Pinal,
    i have a small prblem with merge condition, i am getting error as “Incorrcet syntax near the keyword as”. ,my query is

    MERGE EventHistory AS EH
    USING (select distinct ParcelId, accountno,ImagedDateTime, CreatedDateTime,DelDateTime
    from parcel
    where AccountNo = ’072049′
    AND CreatedDateTime > getdate() -1000
    AND (ImagedDateTime is Not Null or DeldateTime is Not Null))AS PT
    ON PT.ParcelId = EH.ParcelId
    WHEN MATCHED THEN
    UPDATE SET EH.ImagedDateTime = PT.ImagedDateTime , EH.DelDateTime = PT.DelDateTime
    WHEN NOT MATCHED THEN
    INSERT(ParcelId,DelDateTime,CODSentDateTime,ImagedDateTime,PODSentDateTime,LastSentDateTime)
    VALUES(PT.ParcelId,PT.DelDateTime,NULL,PT.ImagedDateTime,NULL,NULL)

    how to resolve it. please help me.

    Reply

  84. hi pinal i am new in sql server
    but i want to ask you that this merge is just like our user defined function, or procedure
    in which we can develop our own logic

    Reply

  85. Thank you for all your help! New to SQL and I end up using your articles from Google searches more then anyone else’s. Very easy to follow!
    Thanks again and keep up the good work!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out / Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out / Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s