0

I have two tables. In which I'm inserting data into the second table from table one with an order by.

IF OBJECT_ID('tempdb..##Table1') IS NOT NULL
    TRUNCATE TABLE ##Table1
ELSE
CREATE TABLE ##Table1 
(
    id varchar(10) PRIMARY KEY CLUSTERED,
    type varchar(10)
)

IF OBJECT_ID('tempdb..##Table2') IS NOT NULL
    TRUNCATE TABLE ##Table2
ELSE 
CREATE TABLE ##Table2 
(
    id_temp varchar(10) PRIMARY KEY CLUSTERED,
    type_temp varchar(10),
    code varchar(10)
)

INSERT INTO ##Table1 
VALUES ('B', 'type2'), ('A', 'type1'), ('C', 'type3')

INSERT INTO ##Table2
    SELECT 
        id AS id_temp, 
        type AS type_temp,
        CASE 
            WHEN type = 'type1' THEN 'Code 1'
            WHEN type = 'type2' THEN 'Code 2'
            ELSE 'Code 3' 
        END AS code
    FROM ##Table1 
    ORDER BY id;

SELECT * FROM ##Table2

Below is the result I'm getting.

id_temp  type_temp  code
---------------------------
B        type2      Code 2
A        type1      Code 1
C        type3      Code 3

I checked online and found that using primary key clustered should work.

I'm new in SQL.

I want Table2 to be sorted by id like this:

id_temp  type_temp  code
--------------------------
A        type1      Code 1
B        type2      Code 2
C        type3      Code 3

Any guidance will be appreciated

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Qwerty
  • 25
  • 5
  • 1
    No primary key in table2? – jarlh Sep 28 '22 at 12:46
  • 1
    Why do you have a varchar column to store integer values? – jarlh Sep 28 '22 at 12:47
  • 3
    @Qwerty the queries work fine. Tables aren't spreadsheets and there's *no* implied order. If you want a specific order you'll have to use ORDER BY. Otherwise the server is free to return rows in the cheapest way possible. That's not a SQL Server bug, that's how all databases work, especially the faster ones. Parallel processing, hash join algorithms, data partitioning mean that rows can come out of the query engine in any order. – Panagiotis Kanavos Sep 28 '22 at 12:54
  • BTW `##Table2` has no primary key. That's a very bad idea because it's impossible to identify rows. Rows aren't stored in any order either. At the very least, with a primary key or clustered index rows are stored in the key's order. – Panagiotis Kanavos Sep 28 '22 at 12:56
  • @PanagiotisKanavos I used ORDER BY in INSERT. Can you tell me where are you saying? – Qwerty Sep 28 '22 at 12:56
  • `order by id` will be ignored in the insert as it has no role when you don't have an `IDENTITY`, `TOP`, `ROWCOUNT`. And even if it wasn't ignored you would still need an `order by` on the SELECT if you wanted them returned in any particular order – Martin Smith Sep 28 '22 at 12:58
  • @PanagiotisKanavos I used the Primary key too. But the result was the same. I have updated the question. – Qwerty Sep 28 '22 at 12:58
  • 1
    `I used ORDER BY in INSERT.` this has no effect in subsequent SELECTs. If you want to read data in a specific order, use an ORDER BY in the SELECT – Panagiotis Kanavos Sep 28 '22 at 12:59
  • `I used the Primary key too.`, no, `##Table2` has no primary key. And once again, there's no order unless you add an explicit ORDER BY – Panagiotis Kanavos Sep 28 '22 at 12:59
  • If you want results in a specific order, add an `ORDER BY` in your *SELECT* query – Panagiotis Kanavos Sep 28 '22 at 13:23

1 Answers1

1

First, let us clarify this:

If you do not explicitly use order by in your query, SQL server does not guarantee an ordered result. If you build something on making such an assumption, you are exposing yourself, your company and anyone involved to humiliation.

On the other hand, I can understand that sometimes you can work with an "untrusted channel" logic, and usually this happens with a primary key. Of course ##Table2 does not have a primary key, so you should add that and it will work....unsafely. Also note that even if you add it, you have to drop ##table2 because it might still be out there.

Clarification on your comment: Using order by on an insert...select does NOT mean that sql server will order the table according to your order by. It is effectively ignored.

George Menoutis
  • 6,894
  • 3
  • 19
  • 43