Joining single column two tables in to another table with two columns and Converting in to Integer?

Question

I have two #temp tables with single column I need to transfer both those columns into new #temp table by converting one column in to INT?

#ProductID
Pid
4
7
8
9
23
88


#Name
Name
tom
jerry
harry
jim
jack
jones

How can insert in to new temp table with two columns and change #ProductID column in to Int data type?

The new temp table should look like:

#Temp1
Pid     name

4        tom

7       jerry

8       harry

9        jim

23     jack

88      jones

jtimperley · Answer 1 · 2013-09-13T18:47:10.147

Assuming 4 goes with 'tom' and 7 goes with 'jerry' in your example and you have SQL Server 2005 or greater. You'll have to use the ROW_NUMBER() function.

This is basically what you'll have to do. Might be slightly off based on the sort of your two tables.

SELECT
    ProductID,
    name
INTO #Result
FROM
(
    SELECT
        Pid,
        ROW_NUMBER() OVER (ORDER BY Fake) AS RowID
    FROM
    (
        SELECT
            Pid,
            1 AS Fake
        FROM #ProductID
    ) InnP
) P
INNER JOIN
(
    SELECT
        Name,
        ROW_NUMBER() OVER (ORDER BY Fake) AS RowID
    FROM
    (
        SELECT
            Name,
            1 AS Fake
        FROM #Name
    ) InnP
) N ON N.RowID = P.RowID;

EDIT: If your original temp table wasn't of type INT then the final result would require 'CONVERT(INT, Pid) AS Pid'. I was assuming you also knew how to create a temp table, just in case you don't before the 'FROM' of your result you can add 'INTO #Whatever' or create your temp table ahead of time and 'INSERT INTO #Whatever'.

EDIT 2: Apparently ROW_NUMBER() doesn't allow you to sort by a constant so the query gets more complicated...

score 0 · Answer 2 · answered Sep 15 '13 at 06:39

if there is no sorting key in a table, there is no way to make sure that you can retrieve the information there by the same order that you inserted in.

I'll assume that you inserted the information in #Name in a given order that you want to keep for future usage. So to keep that order you should have an identity column in the table.

CREATE TABLE #Name 
(
     id INT IDENTITY(1,1),
     [Name] VARCHAR(100)
)

after that you'll be able to join both tables into one.

SELECT CONVERT(INT, Pid) AS nPid, [name]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY CONVERT(INT, Pid ) )as RN , Pid 
    FROM #ProductID) p
INNER JOIN #Name ON p.RN = id

the example in Fiddle can't use temp tables due the nature of fiddle, but otherwise is exactly the as above.

Joining single column two tables in to another table with two columns and Converting in to Integer?

2 Answers2