Q

Can SQL combine tables like these? Doubtful

Can SQL combine tables where the values don't relate to one another? One expert says it's difficult unless you redesign the tables.

I have two tables that look like these:

I would like to create a new table that combines these tables and looks like this:

Can SQL combine tables like these? If so, how?

IT Knowledge Exchange author Denny Cherry responds: There is no reliable way of joining these values because they don't relate to each other. You could, I suppose, join them based on row numbers, but if the rows change order, the joined rows would be different, so that would be very unreliable. The tables need to be redesigned with some sort of relationship between the values.

This was last published in December 2013

Dig Deeper on SQL-Transact SQL (T-SQL)

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

WITH t1 (name, row_num)
AS
(
SELECT name, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_num FROM table1
),
t2 (amount, row_num)
AS
(
SELECT amount, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_num FROM table2
)
SELECT t1.name, t2.amount
INTO table3
FROM t1
JOIN t2 ON t1.row_num = t2.row_num
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close