T-SQL how to quickly make the rows in sequence?

[cuqsy0]

The example table looks like

Table T1
---------------
Name Income
---------------
James 44000
Frank 140000
Robert 60000
---------------

I wish to have another table, which looks like

Table T2
---------------
Name Income
---------------
James 44000
Robert 60000
Frank 140000
---------------

Of course I can use a query that has the wanted order.
--------------------

SELECT Name, Income FROM T1 ORDER BY Income

--------------------
However, I have to bcp out this query to disk and bcp it into Table T2. I think this might be low efficiency.

I can also use a cursor type of T-SQL (the T-SQL is just to show a concept and may not be working)
--------------------------------------

WHILE(SELECT COUNT(*) FROM T1 > 0) 
  BEGIN
    INSERT INTO T2 (NAME, INCOME)SELECT NAME, MIN(INCOME)FROM T1
    DELETE FROM T1 WHERE INCOME = (SELECT MIN(INCOME) FROM T1)
  END

-------------------------------------
Is there a better way to do this?

Thanks

[zmbd]

It would be helpful to understand why you need a second table that duplicates the information in the first table.

I strongly advise against doing this under normal circumstances.

HOWEVER, IF you absolutly have to have this second table, then I suggest a make-table query.. http://msdn.microsoft.com/en-us/libr...v=sql.90).aspx

[cuqsy0]

It would be helpful to understand why you need a second table that duplicates the information in the first table.

I strongly advise against doing this under normal circumstances.

HOWEVER, IF you absolutly have to have this second table, then I suggest a make-table query.. http://msdn.microsoft.com/en-us/libr...v=sql.90).aspx

Thanks for the hint. I have tried to use "select into order by" but the produced table still in the same order as the original table.

The following example illustrate this:

DROP TABLE #test1
DROP TABLE #test2
CREATE TABLE #test1(N INT)

INSERT INTO #test1(N) VALUES (22), (11)
SELECT * FROM #test1

SELECT * INTO #test2 FROM #test1 ORDER BY N

SELECT * FROM #test2

[Rabbit]

Put a clustered index on the table.

I also do not understand the need for this table.

[cuqsy0]

The need for this table is to compare two tables. For example, I have table A and B, like this:

Table A
----------------
Col1 Col2 Col3
3 7 8
1 5 6
1 2 5

Table B
----------------
Col7 Col8 Col9
2 7 8
5 1 6
5 2 1

I need to identify the rows with exactly the same numbers between two tables, although the numbers might be in different columns. In the above example, the rows with the same numbers of the two tables are :
1 5 6

and

1 2 5

Please tell me if there is a better way to do this?

[Rabbit]

Please use code tags when posting code.

I don't see the connection between what you want to do and your original post. The data structure is different, and there's no need for the numbers to be in order.