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.
If the rows in both tables match up, you can merge the columns of one table with another—by pasting them in the first empty cells to the right of the table.
Yes, it is possible to join two tables without using the join keyword.
You could try something like this: SELECT ... FROM ( SELECT f1,f2,f3 FROM table1 UNION SELECT f1,f2,f3 FROM table2 ) WHERE ...
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.
You can't reliably join these two table variables without identities. Assuming they are being populated in an ordinal manner in the first place, each table could be created with identities as follows:
DECLARE @first TABLE(ID INT IDENTITY(1,1), NameFirst VARCHAR(30));
DECLARE @last TABLE(ID INT IDENTITY(1,1), NameLast VARCHAR(30));
-- Note that we don't need to list column names here
INSERT INTO @first VALUES('Little');
INSERT INTO @first VALUES('John');
INSERT INTO @first VALUES('Baby');
INSERT INTO @last VALUES('Timmy');
INSERT INTO @last VALUES('Doe');
INSERT INTO @last VALUES('Jessica');
SELECT n1.NameFirst
, n2.NameLast
FROM @first n1
INNER JOIN @last n2 ON n1.ID=n2.ID;
Result:
NameFirst NameLast
------------------------------ ------------------------------
Little Timmy
John Doe
Baby Jessica
There is no such thing as a "first record". Reading order is totally undefined. This holds true in practice as well! You will see random row order in practice.
You need to define a sorting order. After doing that you can query like this:
SELECT FirstName AS First, LastName AS Last
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS r, FirstName
FROM FirstName
) t1
INNER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS r, LastName
FROM LastName
) t2
ON t1.r = t2.r
Notice the placeholder "SORT ORDER HERE
". Need to fill that in. Example: "ID" or "FirstName".
Edit: Having seen your edit I added sort order on ID. This will work now.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With