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
- 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 you need any further information.