Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - In any performance testing discussion for large development one of the common requirements

  • In any performance testing discussion for large development one of the common requirements has been to send a multiple values to SQL Server in one shot. So to help this requirement, SQL Server introduced the MERGE statement. What is the construct of a MERGE statement? What are the restrictions of using the same?

    Posted on 01-19-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

7  Answers  

Subscribe to Notifications
  • Score
    10

    1. In any performance testing discussion for large development one of the common requirements has been to send a multiple values to SQL Server in one shot. So to help this requirement, SQL Server introduced the MERGE statement.

    Yes, this a very useful technique introduced in SQL Server 2008. A special case is passing multiple values in a table valued parameter to the proc and doing the transaction like insert/update and delete at one shot with respect to the source table values.Excellent tool for ETL.

    2. What is the construct of a MERGE statement?

    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.

    MERGE [INTO] 
    USING 
    ON  (semantics similar to outer join)
    WHEN MATCHED 
    WHEN [TARGET] NOT MATCHED 
    

    Eg:

    CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);
    CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);
    
    --Synchronize source data with target
    MERGE INTO dbo.tbl_Target AS t
        USING dbo.tbl_Source AS s    
            ON t.id = s.id
        WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN
            --Row exists and data is different
            UPDATE SET t.name = s.name, t.qty = s.qty
        WHEN NOT MATCHED THEN 
            --Row exists in source but not in target
            INSERT VALUES (s.id, s.name, s.qty) 
        WHEN SOURCE NOT MATCHED THEN 
            --Row exists in target but not in source
            DELETE OUTPUT$action, inserted.id, deleted.id
    

    3. What are the restrictions of using the same?

    1. The ORDER BY in the merge wont work unless you have a TOP Clause.
    2. SQL Server doesn’t allow a MERGE statement to modify the same row more than once. A MERGE statement will fail if it tries to update the same row more than once, or update and delete the same row.
    3. A MERGE statement may not enforce a foreign key constraint when the statement updates a unique key column that is not part of a clustering key and there is a single row as the update source in SQL Server 2008.(This issue has been fixed in the cumulative update(KB Article:956717)
    4. Indexes may not be used if the MERGE statement has provided with HASH JOIN query hint.
    5. The query optimizer does not apply the simple parameterization process to MERGE statements. Therefore, MERGE statements that contain literal values may not perform as well as individual INSERT, UPDATE, or DELETE statements because a new plan is compiled each time the MERGE statement is executed.
    Replied on Jan 19 2012 2:15AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    9

    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.

    Delete the records whose marks are more than 250. Update marks and add 25 to each as internals if records exist. 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.

    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.

    Restrictions

    • At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.

    • Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints. If IGNOREDUPKEY is set to ON for any unique indexes on the target table, MERGE ignores this setting.

    • The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.

    • When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.

    • MERGE is a fully reserved keyword when the database compatibility level is set to 100. The MERGE statement is available under both 90 and 100 database compatibility levels; however the keyword is not fully reserved when the database compatibility level is set to 90.

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

    • SQL Server doesn’t allow a MERGE statement to modify the same row more than once. A MERGE statement will fail if it tries to update the same row more than once, or update and delete the same row.

    • Requires SELECT permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table.

    • A MERGE statement may not enforce a foreign key constraint when the statement updates a unique key column that is not part of a clustering key and there is a single row as the update source in SQL Server 2008.Microsoft has created the first Hotfix for SQL Server 2008 which will fix this issue. http://support.microsoft.com/kb/956718

    Trigger Implementation

    For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify. For more information about setting trigger firing order, see Specifying First and Last Triggers.

    If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

    If there are any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers defined on target_table, the update or delete operations are not performed. Instead, the triggers fire and the inserted and deleted tables are populated accordingly.

    If there are any INSTEAD OF INSERT triggers defined on target_table, the insert operation is not performed. Instead, the triggers fire and the inserted table is populated accordingly.

    http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete

    http://technet.microsoft.com/en-us/library/bb510625.aspx

    http://www.sqlmag.com/article/tsql3/beyond-merge-s-basics

    Replied on Jan 19 2012 3:45AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    9

    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 advantages of MERGE statement is all the data is read and processed only once.


    How MERGE works

    The MERGE statement basically works as separate insert, update, and delete statements all within the same statement. You specify a “Source” record set and a “Target” table, and the join between the two. You then specify the type of data modification that is to occur when the records between the two data are matched or are not matched. MERGE is very useful, especially when it comes to loading data warehouse tables, which can be very large and require specific actions to be taken when rows are or are not present.

    One of main part of Merge is Table Value Constructor (Transact-SQL) Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement.

    The table value constructor can be specified in the VALUES clause of the INSERT statement, in the USING clause of the MERGE statement, and in the definition of a derived table in the FROM clause.


    Syntax of Merge Please Click:: http://msdn.microsoft.com/en-us/library/bb510625.aspx

    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 ] ) ]
    ;
    

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

    1) Semicolon is mandatory after the merge statement.

    2) When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.


    Limitations and Restrictions

    • Merge join itself is very fast, but it can be an expensive choice if sort operations are required.

    • At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.

    • Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints. If IGNOREDUPKEY is set to ON for any unique indexes on the target table, MERGE ignores this setting.

    • The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.

    • When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client. not giving separate count of individual operation.

    • MERGE is a fully reserved keyword when the database compatibility level is set to 100. The MERGE statement is available under both 90 and 100 database compatibility levels; however the keyword is not fully reserved when the database compatibility level is set to 90.

    • For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last.

    • In SQl 2012, The maximum number of rows that can be constructed using the table value constructor is 1000. To insert more than 1000 rows, create multiple INSERT statements, or bulk import the data by using the bcp utility or the BULK INSERT statement.

    • Only single scalar values are allowed as a row value expression (Plese See Table Value Constructor). A subquery that involves multiple columns is not allowed as a row value expression. For example, the following code results in a syntax error because the third row value expression list contains a subquery with multiple columns.

    • If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

    • If there are any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers defined on target_table, the update or delete operations are not performed. Instead, the triggers fire and the inserted and deleted tables are populated accordingly.

    • If there are any INSTEAD OF INSERT triggers defined on target_table, the insert operation is not performed. Instead, the triggers fire and the inserted table is populated accordingly.

    • Requires SELECT permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table.


    Best example to understant Insert, Update and Delete using merge

    -- Example 1 (Delete,Update,Insert Using Merge)
    -- Create #Stock and #Trades tables
        CREATE TABLE #Stock(Stock varchar(10) NOT NULL, Qty int NOT NULL,) ON [PRIMARY];
        ALTER TABLE #Stock WITH CHECK ADD  CONSTRAINT [CK_Stock] CHECK  (([Qty]>(0)));
    
        CREATE TABLE #Trades(Stock varchar(10) NOT NULL, Delta int NOT NULL,) ON [PRIMARY];
        ALTER TABLE #Trades WITH CHECK ADD  CONSTRAINT [CK_Delta] CHECK  (([Delta]<>(0)));
    
    -- Add some data to the tables
        INSERT #Stock VALUES('MSFT', 10), ('BOEING', 5);
        INSERT #Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3);
    
    -- Check data in both tables
        SELECT * FROM #Stock
        SELECT * FROM #Trades
    
    -- Merge the data
        MERGE #Stock S
        USING #Trades T
        ON S.Stock = T.Stock
        WHEN MATCHED AND (Qty + Delta = 0) THEN
            DELETE -- delete #Stock if entirely sold
        WHEN MATCHED THEN
            -- delete takes precedence over update
            UPDATE SET Qty += Delta
        WHEN NOT MATCHED THEN
            INSERT VALUES (Stock, Delta);
            -- BOEING is deleted, GE inserted, MSFT updated
    
    -- Check data in #Stock tables
        SELECT * FROM #Stock
    
    --Drop Temp Table 
        Drop table #Stock
        Drop table #Trades
    

    MSQL Merge http://msdn.microsoft.com/en-us/library/bb510625(v=sql.110).aspx

    http://forum.kimballgroup.com/t885-ssis-and-the-sql-2008-merge-statement-for-insert-update-and-delete

    Thanks

    Yogesh

    Replied on Jan 19 2012 7:58AM  . 
    Yogesh Kamble
    143 · 1% · 349
  • Score
    7

    MERGE is a newly introduced in sql server 2008.

    Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table

    [ WITH  [,...n] ]
    MERGE 
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ]  [ WITH (  ) ] [ [ AS ] table_alias ]
        USING  
        ON 
        [ WHEN MATCHED [ AND  ]
            THEN  ] [ ...n ]
        [ WHEN NOT MATCHED [ BY TARGET ] [ AND  ]
            THEN  ]
        [ WHEN NOT MATCHED BY SOURCE [ AND  ]
            THEN  ] [ ...n ]
        [  ]
        [ OPTION (  [ ,...n ] ) ]
    ;

    At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause. Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints. If IGNOREDUPKEY is set to ON for any unique indexes on the target table, MERGE ignores this setting. The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator. When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client. MERGE is a fully reserved keyword when the database compatibility level is set to 100. The MERGE statement is available under both 90 and 100 database compatibility levels; however the keyword is not fully reserved when the database compatibility level is set to 90.

    Permissions Requires SELECT permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table. For additional information, see the Permissions section in the SELECT, INSERT, UPDATE, and DELETE topics.

    Example MERGE Production.UnitMeasure AS target USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name) ON (target.UnitMeasureCode = source.UnitMeasureCode) WHEN MATCHED THEN UPDATE SET Name = source.Name WHEN NOT MATCHED THEN INSERT (UnitMeasureCode, Name) VALUES (source.UnitMeasureCode, source.Name);

    Replied on Jan 20 2012 3:05AM  . 
    sk2000
    515 · 0% · 73
  • Score
    8

    MERGE Statement

    MERGE statement allows us to insert, update, or delete data based on certain join conditions in the same statement. In previous versions of SQL Server, we have to create separate statements if you need to insert, update, or delete data in one table based on certain conditions in another table. With MERGE, you can include the logic for these data modifications in one statement. it inserts rows that don’t exist and updates the rows that do exist. With the introduction of the MERGE SQL command, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update or delete.

    The MERGE statement basically works as separate insert, update, and delete statements all within the same statement. You specify a "Source" record set and a "Target" table, and the join between the two. You then specify the type of data modification that is to occur when the records between the two data are matched or are not matched. MERGE is very useful, especially when it comes to loading data warehouse tables, which can be very large and require specific actions to be taken when rows are or are not present.

    MERGE SQL statement improves the performance as all the data is read and processed only once whereas in previous versions three different statements have to be written to process three different activities (INSERT, UPDATE or DELETE) in which case the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.

    The MERGE SQL command looks like as below:

    MERGE <target_table> [AS TARGET]
    USING <table_source> [AS SOURCE]
    ON <search_condition>
    [WHEN MATCHED 
    THEN <merge_matched> ]
    [WHEN NOT MATCHED [BY TARGET]
    THEN <merge_not_matched> ]
    [WHEN NOT MATCHED BY SOURCE
    THEN <merge_ matched> ];
    

    Exanple:

    CREATE TABLE dbo.BookInventory  -- target
    (
      TitleID INT NOT NULL PRIMARY KEY,
      Title NVARCHAR(100) NOT NULL,
      Quantity INT NOT NULL
        CONSTRAINT Quantity_Default_1 DEFAULT 0
    );
    
    CREATE TABLE dbo.BookOrder  -- source
    (
      TitleID INT NOT NULL PRIMARY KEY,
      Title NVARCHAR(100) NOT NULL,
      Quantity INT NOT NULL
        CONSTRAINT Quantity_Default_2 DEFAULT 0
    );
    
    MERGE BookInventory bi
    USING BookOrder bo
    ON bi.TitleID = bo.TitleID
    WHEN MATCHED THEN
      UPDATE
      SET bi.Quantity = bi.Quantity + bo.Quantity;
    
    SELECT * FROM BookInventory;
    

    Restrictions of using MERGE Statement

    • The MERGE SQL statement requires a semicolon (;) as a statement terminator. Otherwise Error 10713 is raised when a MERGE statement is executed without the statement terminator.
    • Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints. If IGNOREDUPKEY is set to ON for any unique indexes on the target table, MERGE ignores this setting
    • When used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted to the client.
    • At least one of the three MATCHED clauses must be specified when using MERGE statement; the MATCHED clauses can be specified in any order. However a variable cannot be updated more than once in the same MATCHED clause.
    • The person executing the MERGE statement should have SELECT Permission on the SOURCE Table and INSERT, UPDATE and DELETE Permission on the TARGET Table.
    • MERGE SQL statement takes same kind of locks minus one Intent Shared (IS) Lock that was due to the select statement in the ‘IF EXISTS’ as we did in previous version of SQL Server.
    • For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify.
    • Merging two very large tables (even if merging with a sub select) is not good
    • Complex code that may require many operational changes
    • MERGE DML operation only implements with single target table or view.Example of you can't insert, delete or update multiple tables using MERGE statement at once.
    • Source table or MERGE source must has to be without duplicated records.
    • difficulty in troubleshooting .

    References

    http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

    http://www.sqlservercentral.com/articles/SQL+Server+2008/64365/

    Replied on Jan 20 2012 3:46AM  . 
    Sineetha
    106 · 2% · 492
  • Score
    7

    MERGE Statement

    Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

    Syntax / Construct

    [ WITH <common_table_expression> [,...n] ]
    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> ] [ ...n ]
        [ 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> ] [ ...n ]
        [ <output_clause> ]
        [ OPTION ( <query_hint> [ ,...n ] ) ]    
    ;
    

    WITH

    Specifies the temporary named result set or view, also known as common table expression, defined within the scope of the MERGE statement. The result set is derived from a simple query and is referenced by the MERGE statement.

    TOP ( expression ) [ PERCENT ]

    Specifies the number or percentage of rows that are affected. expression can be either a number or a percentage of the rows. The rows referenced in the TOP expression are not arranged in any order

    [ INTO ]

    Specifies the table name in which the records are inserted that are efffected by the Merge statement.

    USING

    Specifies the data source that is matched with the data rows in target_table based on . The result of this match dictates the actions to take by the WHEN clauses of the MERGE statement. can be a remote table or a derived table that accesses remote tables.

    ON

    Specifies the conditions on which is joined with target_table to determine where they match.

    WHEN MATCHED THEN

    Specifies that all rows of target_table that match the rows returned by ON , and satisfy any additional search condition, are either updated or deleted according to the clause.

    WHEN NOT MATCHED [ BY TARGET ] THEN

    Specifies that a row is inserted into targettable for every row returned by ON that does not match a row in targettable, but does satisfy an additional search condition, if present. The values to insert are specified by the clause. The MERGE statement can have only one WHEN NOT MATCHED clause.

    WHEN NOT MATCHED BY SOURCE THEN

    Specifies that all rows of target_table that do not match the rows returned by ON , and that satisfy any additional search condition, are either updated or deleted according to the clause.

    The MERGE statement can have at most two WHEN NOT MATCHED BY SOURCE clauses. If two clauses are specified, then the first clause must be accompanied by an AND clause. For any given row, the second WHEN NOT MATCHED BY SOURCE clause is only applied if the first is not. If there are two WHEN NOT MATCHED BY SOURCE clauses, then one must specify an UPDATE action and one must specify a DELETE action. Only columns from the target table can be referenced in .

    Restrictions

    • The MERGE SQL statement requires a semicolon (;) as a statement terminator. Otherwise Error 10713 is raised when a MERGE statement is executed without the statement terminator.
    • When used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted to the client.
    • At least one of the three MATCHED clauses must be specified when using MERGE statement; the MATCHED clauses can be specified in any order. However a variable cannot be updated more than once in the same MATCHED clause.
    • Of course it’s obvious, but just to mention, the person executing the MERGE statement should have SELECT Permission on the SOURCE Table and INSERT, UPDATE and DELETE Permission on the TARGET Table.
    • MERGE SQL statement improves the performance as all the data is read and processed only once whereas in previous versions three different statements have to be written to process three different activities (INSERT, UPDATE or DELETE) in which case the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
    • MERGE SQL statement takes same kind of locks minus one Intent Shared (IS) Lock that was due to the select statement in the ‘IF EXISTS’ as we did in previous version of SQL Server.
    • For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify
    Replied on Jan 26 2012 1:07AM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    9

    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 advantages of MERGE statement is all the data is read and processed only once.


    How MERGE works

    The MERGE statement basically works as separate insert, update, and delete statements all within the same statement. You specify a “Source” record set and a “Target” table, and the join between the two. You then specify the type of data modification that is to occur when the records between the two data are matched or are not matched. MERGE is very useful, especially when it comes to loading data warehouse tables, which can be very large and require specific actions to be taken when rows are or are not present.

    One of main part of Merge is Table Value Constructor (Transact-SQL)

    Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement.

    The table value constructor can be specified in the VALUES clause of the INSERT statement, in the USING clause of the MERGE statement, and in the definition of a derived table in the FROM clause.


    Syntax of Merge Please Click:: http://msdn.microsoft.com/en-us/library/bb510625.aspx

    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 ] ) ]
    ;
    

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

    1) Semicolon is mandatory after the merge statement.

    2) When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.


    Limitations and Restrictions

    • Merge join itself is very fast, but it can be an expensive choice if sort operations are required.

    • At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.

    • Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints. If IGNOREDUPKEY is set to ON for any unique indexes on the target table, MERGE ignores this setting.

    • The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.

    • When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client. not giving separate count of individual operation.

    • MERGE is a fully reserved keyword when the database compatibility level is set to 100. The MERGE statement is available under both 90 and 100 database compatibility levels; however the keyword is not fully reserved when the database compatibility level is set to 90.

    • For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last.

    • In SQl 2012, The maximum number of rows that can be constructed using the table value constructor is 1000. To insert more than 1000 rows, create multiple INSERT statements, or bulk import the data by using the bcp utility or the BULK INSERT statement.

    • Only single scalar values are allowed as a row value expression (Plese See Table Value Constructor). A subquery that involves multiple columns is not allowed as a row value expression. For example, the following code results in a syntax error because the third row value expression list contains a subquery with multiple columns.

    • If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

    • If there are any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers defined on target_table, the update or delete operations are not performed. Instead, the triggers fire and the inserted and deleted tables are populated accordingly.

    • If there are any INSTEAD OF INSERT triggers defined on target_table, the insert operation is not performed. Instead, the triggers fire and the inserted table is populated accordingly.

    • Requires SELECT permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table.


    Best example to understant Insert, Update and Delete using merge

    -- Example 1 (Delete,Update,Insert Using Merge)
    -- Create #Stock and #Trades tables
        CREATE TABLE #Stock(Stock varchar(10) NOT NULL, Qty int NOT NULL,) ON [PRIMARY];
        ALTER TABLE #Stock WITH CHECK ADD  CONSTRAINT [CK_Stock] CHECK  (([Qty]>(0)));
    
        CREATE TABLE #Trades(Stock varchar(10) NOT NULL, Delta int NOT NULL,) ON [PRIMARY];
        ALTER TABLE #Trades WITH CHECK ADD  CONSTRAINT [CK_Delta] CHECK  (([Delta]<>(0)));
    
    -- Add some data to the tables
        INSERT #Stock VALUES('MSFT', 10), ('BOEING', 5);
        INSERT #Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3);
    
    -- Check data in both tables
        SELECT * FROM #Stock
        SELECT * FROM #Trades
    
    -- Merge the data
        MERGE #Stock S
        USING #Trades T
        ON S.Stock = T.Stock
        WHEN MATCHED AND (Qty + Delta = 0) THEN
            DELETE -- delete #Stock if entirely sold
        WHEN MATCHED THEN
            -- delete takes precedence over update
            UPDATE SET Qty += Delta
        WHEN NOT MATCHED THEN
            INSERT VALUES (Stock, Delta);
            -- BOEING is deleted, GE inserted, MSFT updated
    
    -- Check data in #Stock tables
        SELECT * FROM #Stock
    
    --Drop Temp Table 
        Drop table #Stock
        Drop table #Trades
    

    MSQL Merge http://msdn.microsoft.com/en-us/library/bb510625(v=sql.110).aspx

    http://forum.kimballgroup.com/t885-ssis-and-the-sql-2008-merge-statement-for-insert-update-and-delete

    added same answer again because it is not visibile proprerly vieable in preveous one.....

    Thanks

    Yogesh

    Replied on Feb 13 2012 8:02AM  . 
    Yogesh Kamble
    143 · 1% · 349

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.