Efficient / Alternate Inserts with NOT EXISTS or EXISTS condition in SQL Server (UPSERT with MERGE Statement)

Efficient / Alternate Inserts with NOT EXISTS or EXISTS condition in SQL Server (UPSERT with MERGE Statement)

Normally, we are inserting records into a target table, to avoid duplicates, we use a NOT EXISTS or EXISTS condition. This condition work good if the target table has less number of records. 

IF the Target table has millions of records, this exists condition becomes a costly operation as it needs to check with every record in table while inserting the current record. To avoid this performance issue, we make use of MERGE statement in SQL Server. 

In the below query, we can insert when the records into target table when that record is not available and can update the record when it is already available. 

Source Table: col1,col2,joiningCol1,joiningCol2
Target Table: col3,col4,JoiningCol1,joiningCol2

MERGE INTO TargetTable AS Target
 USING(
  SELECT  *
  FROM SourceTable
   WHERE  col1>1)
  AS Source
  ON Source.joiningCol1 = Target.joiningCol1 AND joiningCol2=Target.joiningCol2
 WHEN MATCHED THEN
UPDATE SET Target.col3=Source.col1 AND Target.col4=Source.col2

NOT MATCHED THEN
  INSERT (col3,col4)
  VALUES (Source.col1,Source.col2); 

I have used this query for the table that has a billion records already and the operation took less than a minute to get executed... 

Comments

Post a Comment

Popular posts from this blog

Base 64 encoding and decoding

Encoding and decoding of BASE64 format strings for Apache Tomcat web server.  java -cp /usr/tcServer-6.0/tomcat-6.0.33.B.RELEASE/lib/tcServer.jar:/usr/tcServer-6.0/tomcat-6.0.33.B.RELEASE/bin/tomcat-juli.jar:/usr/tcServer-6.0/tomcat-6.0.33.B.RELEASE/lib/tomcat-coyote.jar com.springsource.tcserver.security.PropertyDecoder -encode / -decode base64 <password>
Read more

LINQ Queries with GROUP BY, INNER JOIN, COUNT and SUM: Examples

Examples of different LINQ queries in C#: GROUP BY, INNER JOIN, COUNT and SUM Setup the LINQPad against the Northwind database. 1. LINQ query with GROUP BY, JOIN and SUM.     Find the quantities required for each product to fulfill all the orders.     Here the tables we have to use are Products and Order Details. Both of these tables can be joined on    ProductID key and grouped by Product Name to make sure that the Quantity is calculated against a named product instead of ID.     The SQL query for the above requirement is :        s elect A.ProductName,sum(B.Quantity)         from Products P join "Order Details" OD         on A.ProductID=B.ProductID         group by A.ProductName     The equivalent LINQ query for the above is:                 from p in Products         join od in OrderDetails         on p.ProductID equals od.ProductID         group od by p.ProductName into g         select new { ProductName=g.Key, Quanti=g.Sum(a => a.Quantity)}      2. LIN
Read more

How to write Custom delete Confirmation Modal for Kendo Grid in MVC:

How to write Custom delete Confirmation Modal for Kendo Grid in MVC: For the above task, we need to add custom command for delete operation.For the Click, the argument is the javascript function name. .Columns(columns =>columns.Command(command => { command.Custom("Delete").Click("deleteRow"));  Later, you need to tell the kendo Grid that you want to suppress the default behavior for the Delete Action. .Editable(editable => editable.DisplayDeleteConfirmation(false)) Need to remove the destroy property for the datasource mapping to grid. Like .Destroy for the DataSource has to be removed. We are handling this server side work through script logic. Now, write the modal window markup for the Delete pop-up. <div id="modalWindow">     <h2>Are you sure to Delete?</h2>     <span class="k-label">Reason For Deletion</span> <input id="txtReasonForChange" class="k-textbox" />    
Read more