SQL-Server Merge Statement with .NET DataTable

Question

Background: I'm trying to synchronize order information between Oracle and SQL-Server databases and allow users to be able to track any changes between what has been replicated via an ASP.NET web page. Users will be able to select which line items they want to be able to work on via this page and the remainder will be excluded going forward. Users will also have the option to change which items have been excluded at any time until the process has been finalized.

The initial load of this order information replicates (from Oracle to SQL-Server) all of the line items and presents them for assignment to the user. Any subsequent load of the order will compare replicated data to the original data. I'm using the following statement to handle the replication and reconciliation based on user selections:

CREATE TABLE #tempSpecOrderReplication 
( 
    Ord_L_ID INT, 
    Order_Number INT, 
    Ord_ID INT, 
    Item_Number VARCHAR(50), 
    Quantity DECIMAL(18, 2), 
    UOM VARCHAR(50), 
    Price DECIMAL(18, 2), 
    Is_Spec_Order BIT  
); 

INSERT INTO #tempSpecOrderReplication 
([Ord_L_ID], [Ord_ID], [Quantity], [UOM], [Price], 
 [Is_Spec_Order], [Item_Number], [Order_Number]) 
VALUES (...); --These values come from the .NET DataTable

MERGE Spec_Order_Replication WITH (HOLDLOCK) AS target 
USING (SELECT * FROM #tempSpecOrderReplication) AS source 
ON (target.Ord_L_ID = source.Ord_L_ID) 
WHEN MATCHED THEN 
    UPDATE SET Is_Spec_Order = source.Is_Spec_Order 
WHEN NOT MATCHED THEN 
    INSERT 
    (Ord_L_ID, Order_Number, Ord_ID, Item_Number, 
     Quantity, UOM, Price, Is_Spec_Order) 
    VALUES 
    (source.Ord_L_ID, source.Order_Number, source.Ord_ID, source.Item_Number, 
     source.Quantity, source.UOM, source.Price, source.Is_Spec_Order); 

DROP TABLE #tempSpecOrderReplication 

Questions:

1. Is there any way I can improve the existing SQL statement?
2. Is there a better way that I can go about this?

I'm using SQL-Server version 2008 R2.

Please ask if you need any further information.

Answer 1

Well done.

I honestly cannot find anything bad to say about your SQL code. Your capitalization of keywords and indentation are consistent. Your query is properly explicit, there is no guessing work that the SQL engine would have to make. You clean up after your operation by dropping your #tempSpecOrderReplication.

The only thing that looked unusual, though not bad, is that you used square brackets in this clause but nowhere else. I would personally prefer if it were consistent thoughout, if I had to maintain it. But it's just a nitpick.

INSERT INTO #tempSpecOrderReplication 
([Ord_L_ID], [Ord_ID], [Quantity], [UOM], [Price], 
 [Is_Spec_Order], [Item_Number], [Order_Number]) 
VALUES (...); --These values come from the .NET DataTable

Answer 2

I don't know that I would create a Temporary table to do this.

You have two things that you are doing here

1. Inserting if the record doesn't exist
2. updating the record if a record with the same ID already exists

I think that instead of a MERGE I would just use an If Then Statement. I always think about keeping it simple, and I think that using an If Statement would be much simpler than trying to Merge a Temp Table into an Existing table.

It would look something like this

DECLARE @Ord_L_ID INT --a very important input variable
DECLARE @Is_Spec_Order BIT
DECLARE @Order_Number INT
DECLARE @Ord_ID INT
DECLARE @Item_Number INT
DECLARE @Quantity INT
DECLARE @UOM INT -- Wasn't sure what this was Guessed it was another INT
DECLARE @Price Decimal -- There is also Money and SmallMoney DataTypes

-- ETC.

IF EXISTS (SELECT * FROM Spec_Order_Replication AS SOR WHERE SOR.Ord_L_ID = @Ord_L_ID)
    UPDATE Spec_Order_Replication
    SET 
        Is_Spec_Order = @Is_Spec_Order
    WHERE 
        Ord_L_ID = @Ord_L_ID
ELSE
    INSERT INTO Spec_Order_Replication
    (
        @Ord_L_ID
        , @Order_Number
        , @Ord_ID
        , @Item_Number
        , @Quantity
        , @UOM
        , @Price
        , @Is_Spec_Order
    )

This should be housed in a stored procedure so that you can call it from your Application using whatever data is sent to it.

Forcing the assignment of the table's Primary Key is a little weird but makes sense in this circumstance, but be careful because it could cause issues.

---

Your code does this

• takes a comma delimited list
• creates a temporary table
• inserts records into the Temporary Table
• Merges a permanent table to a Temporary Table on an ID
  • ID may or may not exist
• finds the matches
  • updates table
• finds where there are no matches
  • inserts into permanent table

my code does this

• takes in data for a row
• determines if data exists in permanent table
  • if it exists the permanent table is updated
  • if it doesn't exist new data is inserted into the permanent table

My code does far less than your code, and as such will be much more efficient and take up less processing on the server, regardless of the fact that the procedure will be called once for every row of data.