How to join two tables together with same number o

Question

I am using SQL2000 and I would like to join two table together based on their positions

For example consider the following 2 tables:

table1
-------
name
-------
'cat'
'dog'
'mouse'

table2
------
cost
------
23
13
25

I would now like to blindly join the two table together as follows based on their order not on a matching columns (I can also guarantee both tables have the same number of rows):

-------|-----
name   |cost
-------|------
'cat'  |23
'dog'  |13
'mouse'|25

Is this possible in a T-SQL select??

Answer 1

in 2000 you will either have to run 2 forward only cursors and insert into a temp table. or insert the values into a temp table with an extra identity column and join the 2 temp tables on the identity field

Answer 2

Absolutely. Use the following query but make sure that (order by) clause uses the same columns the order of rows will change which you dont want.

select
(
row_number() over(order by name) rno, * from Table1
) A  
(
row_number() over(order by name) rno, * from Table2
) B
JOIN A.rno=B.rno

order by clause can be modified according to user linkings

The above query produces unique row_numbers for each row, which an be joined with row_numbers of the other table

Answer 3

Do you have anything that guarantees ordering of each table?

As far ax I know, SQL server does not make any promise on the ordering of a resultset unless the outer query has an order by clause. In your case you need Each table to be ordered in a deterministic manner for this to work.

Other than that, in SQL 2000, as answered before me, a temp table and two cursors seem like a good answer.

Update: Someone mentioned inserting both tables into temp tables, and that it would yield better performance. I am no SQL expert so I defer to those who know on that front, and since I had an up-vote I thought you should investigate those performance considerations. But in any case, if you do not have any other information in your tables than what you showed us I'm not sure you can pull it off, ordering-wise.