How to insert bulk data without changing order of item into table using merge statement

Question

I wrote a stored procedure that can insert bulk data into table using the merge statement.

Problem is that when I insert itemid 1024,1000,1012,1025 in this order, then SQL Server automatically changes order of itemid 1000,1012,1024,1025.

I want to insert data that I actually pass.

Here is sample code. This will parse XML string into table object:

DECLARE @tblPurchase TABLE
            (
              Purchase_Detail_ID INT ,
              Purchase_ID INT ,
              Head_ID INT ,
              Item_ID INT 
            );


INSERT INTO @tblPurchase (Purchase_Detail_ID, Purchase_ID, Head_ID, Item_ID)
    SELECT  
        Tbl.Col.value('Purchase_Detail_ID[1]', 'INT') AS Purchase_Detail_ID,
        Tbl.Col.value('Purchase_ID[1]', 'INT') AS Purchase_ID,
        Tbl.Col.value('Head_ID[1]', 'INT') AS Head_ID,
        Tbl.Col.value('Item_ID[1]', 'INT') AS Item_ID 
    FROM    
        @PurchaseDetailsXML.nodes('/documentelement/TRN_Purchase_Details') Tbl(Col)

This will insert bulk data into the TRN_Purchase_Details table:

MERGE TRN_Purchase_Details MTD
USING (SELECT    
           Purchase_Detail_ID,
           Id AS Purchase_ID,
           Head_ID, Item_ID 
       FROM      
           @tblPurchase
       LEFT JOIN 
           @ChangeResult ON 1 = 1) AS TMTD ON MTD.Purchase_Detail_ID = TMTD.Purchase_Detail_ID
                                           AND MTD.Purchase_ID = TMTD.Purchase_ID

WHEN MATCHED THEN
    UPDATE SET MTD.Head_ID = TMTD.Head_ID,
               MTD.Item_ID = TMTD.Item_ID 

WHEN NOT MATCHED BY TARGET THEN
    INSERT (Purchase_ID, Head_ID, Item_ID)
    VALUES (Purchase_ID, Head_ID, Item_ID)

WHEN NOT MATCHED BY SOURCE AND       
     MTD.Purchase_ID = (SELECT TOP 1 Id
                        FROM @ChangeResult
                        WHERE Id > 0) THEN
    DELETE;

A relational table is **by definition** an **unordered set** of data - you **cannot** except / preserve any "ordering" after insert. The ***ONLY*** way to get ordering is when you run a `SELECT` from that table and you **explicitly specify** an `ORDER BY` clause. Anything else is just coincidental "ordering" - not reliable, not reproducible - just coindidence — marc_s, Dec 24 '18 at 09:28

Andomar · Accepted Answer · 2018-12-24T13:21:08.820

4

Rows in a SQL table don't have any order. They come back in indeterminate order unless you specify an order by.

Try adding an identity column to your temporary table?

DECLARE @tblPurchase TABLE
        (
          ID int identity,
          Purchase_Detail_ID INT ,

The identity column might capture the order of the XML elements.

If that doesn't work, you can calculate the position of the elements in the XML and store that position in the temporary table.

edited Dec 24 '18 at 13:21

answered Dec 24 '18 at 09:28

Andomar

232,371
49
380
404

. . They are returned in an *indeterminate* order, not a *random* order. The latter is quite well defined, and SQL generally does randomize the result set. – Gordon Linoff Dec 24 '18 at 13:17
@GordonLinoff: The [dictionary definition](https://www.merriam-webster.com/dictionary/random) of random is "without definite aim, direction, rule, or method" which seems appropriate. Agree that indeterminate is a better term, I'll update the answer – Andomar Dec 24 '18 at 13:22

score 0 · Answer 2 · answered Dec 24 '18 at 09:37

As mentioned elsewhere, data in a table is stored as an unordered set. If you need to be able to go back to your table after data is inserted and determine the order that it was inserted, you'll have to add a column to the table schema to record that information.

It could be something as simple as adding an IDENTITY column, which will increment on each row addition, or perhaps a column with a DATETIME data type and a GETDATE() default value so you not only know the order rows were added, but exactly when that happened.

How to insert bulk data without changing order of item into table using merge statement

2 Answers2