How to ORDER BY and INSERT to TABLE when loading data from JSON

Question

I need help to load data from JSON to a table but have them ordered/sorted and insert to table?

DECLARE @IN_DATESJSON NVARCHAR(MAX) = N'[{"CreatedDate":"2018-10-10T09:07:29Z"},{"CreatedDate":"2018-10-09T09:07:29Z"},{"CreatedDate":"2018-10-08T07:07:08Z"}]';
DECLARE @V_CALLSTBL AS TABLE (CreatedDate DATETIME);
IF (ISJSON(@IN_DATESJSON) = 1)
    BEGIN  
        INSERT INTO @V_CALLSTBL
        SELECT *
        FROM OPENJSON (@IN_DATESJSON)
        WITH (CreatedDate DATETIME2)
        -- ORDER BY CreatedDate ASC -- THIS DOESN'T WORK*
    END
SELECT * FROM @V_CALLSTBL;

CreatedDate
-----------------------
2018-10-10 09:07:29.000
2018-10-09 09:07:29.000
2018-10-08 07:07:08.000

Related: [How to maintain the order of insertion in SQL Server](https://stackoverflow.com/questions/41917452/how-to-maintain-the-order-of-insertion-in-sql-server), [Preserving ORDER BY in SELECT INTO](https://stackoverflow.com/questions/14424929/preserving-order-by-in-select-into), [Return rows in the exact order they were inserted](https://stackoverflow.com/questions/17345731/return-rows-in-the-exact-order-they-were-inserted) — Ilja Everilä, Oct 11 '18 at 06:14
[Populate table variable with ordered results](https://stackoverflow.com/questions/42277922/populate-table-variable-with-ordered-results) — Ilja Everilä, Oct 11 '18 at 06:39
@IljaEverilä can you please explain this behaviour: [Image Link](https://imgur.com/a/7sxKPvd) — Chenna, Oct 11 '18 at 07:47
There's not much gained by explaining it, since the order of a SELECT that does not explicitly use ORDER BY is unspecified, which does not mean it cannot appear ordered, but you cannot rely on it. Tables are also inherently unordered bags, since they're modeled after relations, so "insertion order" has no meaning. — Ilja Everilä, Oct 11 '18 at 09:23

Serkan Arslan · Accepted Answer · 2018-10-11T06:16:46.577

2

Actually, it inserts ordered but you should also select it with order by. Because select not guaranteed to return sorted data as inserted.

SELECT * FROM @V_CALLSTBL ORDER BY CreatedDate ASC;

and you can add an identity column to the table for seeing this.

DECLARE @IN_DATESJSON NVARCHAR(MAX) = N'[{"CreatedDate":"2018-10-10T09:07:29Z"},{"CreatedDate":"2018-10-09T09:07:29Z"},{"CreatedDate":"2018-10-08T07:07:08Z"}]';
DECLARE @V_CALLSTBL AS TABLE (ID INT IDENTITY(1,1), CreatedDate DATETIME);
IF (ISJSON(@IN_DATESJSON) = 1)
    BEGIN  
        INSERT INTO @V_CALLSTBL
        SELECT *
        FROM OPENJSON (@IN_DATESJSON)
            WITH (CreatedDate DATETIME2)
        ORDER BY CreatedDate ASC -- THIS DOESN'T WORK*
    END
SELECT * FROM @V_CALLSTBL ORDER BY CreatedDate ASC;

edited Oct 11 '18 at 06:16

answered Oct 11 '18 at 06:10

Serkan Arslan

12,345
4
22
38

This worked, can I know how does adding extra `ID` column make this work ? – Chenna Oct 11 '18 at 06:37
1

If you want to get a sorted result, you have to add `order by` to your `select`. Adding an identity column does not change anything. I just added it to show you your data inserted by sorted. – Serkan Arslan Oct 11 '18 at 06:47
2

There are two cases; one with an identity column where the select returns data in date with insert order and the second is only with date field and data is returned in the opposite order. This actually is not a definite behaviour. As in the answers you should somehow explicitely use ORDER BY clause. – Eralper Oct 11 '18 at 10:23

score 1 · Answer 2 · answered Oct 11 '18 at 06:18

If you don't specify an ORDER BY clause in your SELECT statement, it is not guaranteed that the returned data set will be in sorted.

Additionally, to keep a specific table data ordered, you should specify a cluster index on that table column. This will be used for fetching the data faster when a search is made on that column is in the filter criteria.

Again there is no sorting guaranteed if you don't use an ORDER BY clause

How to ORDER BY and INSERT to TABLE when loading data from JSON

2 Answers2