CBTr.Net Tech Dump

a co-worker today asked if there was a way to insert data from one table into another but sort the columns by ASC for BOTH columns. For example, tableA has the following value:

rowid: 1,2,3,4
columnA: 3,7,1,6
columnB: 6,7,2,4

In his new table, he wants it to look like so (tableB):

rowid: 1,2,3,4
columnA: 1,3,6,7
columnB: 2,4,6,7

@ first, it should be easy right?

Source code

INSERT INTO tableB(columnA, columnB)
SELECT columnA, columnB FROM tableA ORDER BY columnA, columnB

But that will only sort by columnA in asc. ColumnB will not be sorted at all. Instead I ended up doing:

Source code

INSERT INTO tableB(columnA)
SELECT columnA FROM tableA ORDER BY columnA

The above statement gives me the 1st column in ASC order, now for the 2nd:

Source code

UPDATE v1 SET v1.columnB=v.columnB FROM
(SELECT ROW_NUMBER() OVER (ORDER BY columnB) AS RowNumber, columnB
FROM tableA ) v
INNER JOIN tableB v1 ON v.RowNumber=v1.Rowid