How to combine columns from 2 tables into 1 without using JOIN

sql, sql-server, t-sql

Solution

You could try something like this, to match up based on row numbers:

SELECT FirstName AS First, LastName AS Last
FROM
(
  SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum, FirstName
  FROM FirstName
) t1
INNER JOIN
(
  SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum, LastName
  FROM LastName
) t2
ON t1.RowNum = t2.RowNum

But don't take this as a signal that you don't need keys.

Problem

I am using SQL Server 2008. I have 2 table variables like ``` FirstName ========== Little John Baby LastName ========== Timmy Doe Jessica ``` And I want the result table be: ``` First Last ===================== Little Timmy John Doe Baby Jessica ``` Note that there is no PK can join the 2 tables. I am trying to use a cursor but not sure how to start. ---- Updated ----- I know it's a rare case, but I am writing a script to clean up legacy data. The only way we know "Little" goes with "Timmy" is that they are both the first record of the table. Would it help if we had PK for the tables but there is no relation? ``` ID FirstName ========== 1 Little 2 John 3 Baby ---------- ID LastName ========== 4 Timmy 5 Doe 6 Jessica ---------- ``` I am not familiar with TSQL so I thought I can loop through the 2 tables like looping through Arrays in memory.

Original source