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
##Table2has 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.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