August 8, 2011 at 12:46 pm
Hi All,
Would it be a good idea to convert all my insert statements (which loads static data) to merge statements? It feels like there is a lot more flexibility with using merge statements - especially if the data is already populated in the target table. Is there a massive performance difference?
One negative I could see is that a merge statement isn't as easy to read as an insert statement for people that don't quite understand how a merge statement works (but is that really our problem? 🙂 )
Opinions?
Cheers,
August 8, 2011 at 1:05 pm
In the group I am with now, we use MERGE in all SSIS packages as an unwritten standard.
I have never seen any measurable difference between MERGE and INSERT.
August 8, 2011 at 2:08 pm
I quote Dave Pinal: "One of the most important advantages of MERGE statement is that the entire data are read and processed only once. In earlier versions, three different statements had to be written to process three different activities (INSERT, UPDATE or DELETE); however, by using MERGE statement, all the update activities can be done in one pass of database table."
See his opinion on this topic here: http://blog.sqlauthority.com/2010/06/08/sql-server-merge-operations-insert-update-delete-in-single-execution/
August 8, 2011 at 2:20 pm
hxkresl (8/8/2011)
I quote Dave Pinal: "One of the most important advantages of MERGE statement is that the entire data are read and processed only once. In earlier versions, three different statements had to be written to process three different activities (INSERT, UPDATE or DELETE); however, by using MERGE statement, all the update activities can be done in one pass of database table."See his opinion on this topic here: http://blog.sqlauthority.com/2010/06/08/sql-server-merge-operations-insert-update-delete-in-single-execution/
Very True,
But ifs the intent is only to insert data, would it make sense to write it as a merge statement? After all, writing an insert statement in the new 2008 format inserts all rows in 1 transaction....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply