0

I've been searching and I've yet to find an example using merge for populating related tables.

The Northwind DB Order & OrderDetail tables could be used. (In our scenario, our tables are 3 levels deep.)

For simplicity let's say we have the following tables.

Orders

OrderID PK
OrderNumber

OrderDetails

OrderID - PK
OrderLineItemNumber PK - FK to Orders.OrderID field

OrderDetailDetails

OrderID - PK - FK to OrderDetails.OrderID
OrderLineItemNumber - PK - FK to OrderDetails.OrderLineItemNumber
OrderSequenceNumber - PK

Also, in this scenario, records get written to staging tables that are identical the tables above. The merge would need to merge records from the 3 staging tables to the 3 matching production tables.

The production Order table's OrderId will not share the staging Order tables OrderId value.

So if the merge conditions are met, then there must be an insert into the Order table to generate OrderId (set to identity) because OrderId is needed for the OrderDetail & OrderDetailDetails rows to be created.

Right now I've written a service in C# that does all this but it's not that performant.

MERGE was discovered so we're looking into it to see if it can be used in a situation such as this. Any tips or pointers would be greatly appreciated.

Thanks.

Edit: I am now using Output store values into a Temporary table called @MergeOutput.

Declare @MergeOutput Table
(
      ActionType varchar(10),
      InsertedOrderId int,
      StagingOrderID int,
      DeletedOrderId int
);

However, I need to do a Merge on all 3 tables.  (Order, OrderDetail & OrderDetailDetails)

Also, these tables have more fields than just the Id's.

So I've started creating the 2nd Merge for the OrderDetail table.

MERGE OrderDetail AS OD
USING(
    SELECT OrderID,
          OrderLineItemNumber,
      ProductId
   FROM OrderDetail AS OD
   where OrderId IN (Select StagingOrderID from @MergeOutput where ActionType = 'INSERT'
) AS src(OrderID,
      OrderLineItemNumber,
      ProductId
)
ON (OD.OrderId = src.Order AND OD.OrderLineItemNumber = src.OrderLineItemNumber)
WHEN NOT MATCHED By Target THEN

INSERT INTO <-- (This doesn't work no matter what I've tried so far.)
  Select (Select Distinct InsertedOrderID from @MergeOutput where StagingOrderId = OrderID), src.OrderLineItemNumber, src.ProductId
;

I see the following errors with the code above. "Incorrect syntax near the keyword 'into'

I need the functionality of the Merge to move records on all 3 tables

Looks like I've finally got this to working. I had to change the Insert statement to as follows.

Insert(OrderId, OrderLineItemId, ProductID)
Values((Select Distinct InsertedOrderID from @MergeOutput where StagingOrderId = OrderID), src.OrderLineItemNumber, src.ProductId)

I had tried this Insert statement earlier on. I just figured out I had to wrap the selection parens ().

Thanks for everyone's help. I'm hoping I can carry this over to the merge for the 3rd table.

kfrosty
  • 805
  • 1
  • 8
  • 14
  • **What** database and which version?? **SQL** is just the Structured Query Language - a language used by many database systems - SQL is **NOT** a database product... stuff like this is very often vendor-specific - so we really need to know what database system you're using.... – marc_s Jan 21 '12 at 22:53
  • You seem to be misunderstanding the concept of `OUTPUT ... INTO`. Please have a look at this question: [Using merge..output to get mapping between source.id and target.id](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id), which, as far as I can see, is fundamentally about what you are trying to do. You might also need to have the [`MERGE` manual](http://msdn.microsoft.com/en-us/library/bb510625.aspx "MERGE (Transact-SQL)") at hand, just in case. – Andriy M Jan 22 '12 at 06:00
  • Have just seen your problem with the insert. It was a syntactic issue. Let us know if it worked for the 3rd table. If not, I'll be helping you. – usr Jan 22 '12 at 10:42
  • Hello Andriy, I do understand Output and INTO. I found this article shortly after my original post. http://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/ The original problem was a syntactical error trying to use the Select WHEN NOT Matched statement of my second merge trying to process the Staging OrderDetail records. I thought everything was fine. However, I just realized after there is another flaw here and it's when there are matching records in the tables. If you look at the On condition in the second merge, OD.OrderID will never equal src.OrderID. – kfrosty Jan 22 '12 at 14:13
  • (Cont.) When the Merge is dealing existing records that need to be updated. Actually with the Staging_Tables not sharing a PK with the production tables, none of this works for updates. Right now I have a RecordStateTypeId. Before I run the first merge, run several Update states that first Validate that all the proper fields are populated in the Staging tables. If there are invalid fields, the the records get marked as a state of Rules_Failed. 2nd update marks the valid staging records to a state of Rules_Pending. First Merge selects staging records = Rules_pending. Working on the rest. – kfrosty Jan 22 '12 at 14:16
  • Right now I'm thinking a viable alternative is instead of using bigint as an Identity for the Order PK fields or instead using UniqueID for OrderID in both Staging Orders & Order's for OrderID field so to my knowledge we don't have to worry about duplicates. Set the Default Value to newid(). This way if we ever have the need to write records directly to the Order table without going through Staging we can as well. FYI. We have staging because we're pulling records from legacy systems into a new system. New records go into staging to get validated before going into production tables. – kfrosty Jan 22 '12 at 14:27
  • For any interested, without changing OrderID to a UniqueID in staging and production. For now, the Staging_Order table has a StagingOrderStateId field. A WCF service is responsible for putting all the orders in Staging. When it's inserts a record, the state is WCF_Success. When my sproc runs, I first run an Update and set all the staging orders in a state of WCF_Success into a state of Rules_Pending. Then my MERGE's are based on records with this state. For orders that fail validate, another update sets their state to Rules_Failed. Upon success, the state is Rules_Processed. – kfrosty Jan 23 '12 at 15:00

1 Answers1

0

This is a nasty problem that keeps coming up. You need to extract the inserted identity values. In SQL Server you can do this using the OUTPUT clause (http://msdn.microsoft.com/en-us/library/ms177564.aspx) with the INSERT "virtual table". This allows you to get all inserted IDs out in one statement.

You can then push the IDs into a temp table and use them to insert the detail records with the appropriate master IDs like this:

INSERT INTO Detail
SELECT * from Staging_Detail
JOIN #MasterIDs on Staging_Detail.MasterID = #MasterIDs.MasterID
usr
  • 168,620
  • 35
  • 240
  • 369
  • MS SQL 2008 R2. Since I posted, I've figured out how to use Output to populate a temporary table when the Staging_Order Table is merged to Order. Keep in mind the tables each have a lot more fields. I'm just trying to keep this simple. So now I've created the Order record and the Staging records OrderId that I can map to the new OrderId. So I start another Merge table for the OrderDetail records. The problem I'm at now is I need fields from the Merge Using and the new OrderID. (I apologize but this site won't let me hit enter for new paragraphs.) Merge doesn't appear to allow into. – kfrosty Jan 22 '12 at 04:59
  • @FrostKris: As can be seen from the doc page linked by usr, `MERGE` does allow `INTO` (i.e. if you mean `OUTPUT ... INTO`). Also, of all the commands allowing the `OUTPUT` clause, `MERGE` is the only one that lets you reference not only `INSERTED`'s or `DELETED`'s columns, but also the source table's columns. Did you try it? – Andriy M Jan 22 '12 at 05:14
  • @FrostKris: Oops, the second statement in my previous comment is not very accurate, sorry: `UPDATE` and `DELETE` allow referencing the source too. Still, the main point is, you *can* do that with `MERGE`, which is relevant to your problem. – Andriy M Jan 22 '12 at 05:20
  • I'm having trouble using an Insert in a WHEN NOT MATCHED clause. I've edited my original post to try and make things clearer. – kfrosty Jan 22 '12 at 05:44