Insert into table from another table after sorting data of first table

Question

I am doing something like this in SP,

insert into @FilteredTbl select * from @MasterTbl

but now problem is that i want data of @MasterTbl sorted before adding into @FilteredTbl,

How can I do that ?

insert into at FilteredTbl (select * from (select * from at MasterTbl order by field) as a) — Sanjay Goswami, Mar 27 '12 at 06:33
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/aeb1053d-0dc2-4dc3-96f5-8b213fed9669 — Somnath Muluk, Mar 27 '12 at 06:34
http://stackoverflow.com/questions/263101/insert-into-temp-values-select-order-by-id — Somnath Muluk, Mar 27 '12 at 06:36
Possible duplicate of [Preserving ORDER BY in SELECT INTO](http://stackoverflow.com/questions/14424929/preserving-order-by-in-select-into) — Athafoud, Aug 22 '16 at 14:49

score 4 · Accepted Answer · answered Mar 27 '12 at 06:31

4

Maybe something like this:

INSERT INTO @FilteredTbl 
SELECT * FROM @MasterTbl AS tbl
ORDER BY tbl.OrderbyColumn

answered Mar 27 '12 at 06:31

Arion

31,011
10
70
88

7

Am I missing something here....aren't all these solutions incorrect? The order is not guaranteed. _"4. INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted"_ http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx – user3811205 Mar 03 '15 at 23:41
Yes the above Query wont work as explained in the above link. – maxspan May 04 '16 at 13:25

score 3 · Answer 2 · answered Sep 08 '16 at 10:16

3

Try following method to insert sort result into temp table. Use ROW_NUMBER to ORDER BY table result

DECLARE @FilteredTbl AS TABLE
(
    RowID INT
    [Column_1]
    [Column_2]
      .
      .
)

insert into @FilteredTbl 
select ROW_NUMBER()OVER(ORDER BY [ShortColumn]),[Field_1],[Field_2]... from @MasterTbl

answered Sep 08 '16 at 10:16

Jaylem Chaudhari

86
1
2

With out "ROW_NUMBER()", inserting data into a temp table with ordering is not working. working only with "ROW_NUMBER()" . thanks Jaylem – Sajith A.K. Oct 25 '17 at 11:22

score 0 · Answer 3 · edited Sep 01 '23 at 08:48

0

I had the same problem and solved it with adding id as identity to the declared table

in your case:

@FilteredTbl
 ID int identity not null,
 your columns...

When inserting sorted data it still sorted as it was

edited Sep 01 '23 at 08:48

mozway

194,879
13
39
75

answered Sep 01 '23 at 08:47

Qusay

1
2

score -1 · Answer 4 · edited Jan 07 '16 at 08:24

-1

You should add identity your table then It will sorted like that

CREATE TABLE #test (
    s int IDENTITY (1, 1),
    ID int,
    Name varchar(50),
    part int
);

INSERT INTO  #test (ID,Name,Part)
SELECT ID, Name, Part FROM Table_1 ORDER BY ID DESC
SELECT * FROM #test

edited Jan 07 '16 at 08:24

KittMedia

7,368
13
34
38

answered Jan 07 '16 at 08:05

Eray

1
1

minus1 We don't need any extra column here as the table already has it's primary key. – IT ppl Jan 07 '16 at 11:55

score -1 · Answer 5 · answered Mar 27 '12 at 06:34

-1

You can use order by class in select statement

insert into @FilteredTbl 
    select * from @MasterTbl
    order by <column name>

answered Mar 27 '12 at 06:34

Sathya Narayanan

857
5
8

Kishore Kumar · Answer 6 · 2012-03-27T06:43:27.823

-1

Please sort the table as you want before inserting into another table.

Like:

Select * From @MasterTbl m Order By m.ColumnName

then insert into your new table

Insert Into @FilterTbl Select * From @MasterTbl m Order By m.ColumnName [DESC]

You can also filter your @MasterTbl using Where Clause

edited Mar 27 '12 at 06:43

answered Mar 27 '12 at 06:35

Kishore Kumar

12,675
27
97
154

score -1 · Answer 7 · answered Mar 27 '12 at 06:36

-1

INSERT INTO @FilteredTbl SELECT * FROM @MasterTbl AS mtb ORDER BY mtb.OrderbyColumn [desc]

Here desc is optional if you want to order by your column in descending order than add ir otherwise no need to add

answered Mar 27 '12 at 06:36

raina

81
1
3
8

Insert into table from another table after sorting data of first table

7 Answers7