SQL-Server Merge Statement with .NET DataTable

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:

• Is there any way I can improve the existing SQL statement?

• Is there a better way that I can go about this?

I'm using SQL-Server version 2008 R2.

Please ask if yo

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

StackExchange Code Review Q#66901, answer score: 6