Vertical Tables Merge

Question

Post reply

Vertical Tables Merge

mpradeesh

SSC Enthusiast

Points: 168
More actions
July 20, 2010 at 9:20 am

#224998

I have two tables with

Table1(Id int)

Table2(Name varchar(20))

No of Rows are same. Lets say 10 records each in both tables.

I want to select Id and Name from both table in a single select and retrieve 10 records.

meaning the Id in the position1 of Table1 corresponds to Name in the Postion 1 in the Table 2.

There is no primary/foreign key relationships and so no joins please. The key is position mapping.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

There's no such thing as position 1 in table1 or table2. The order in which records were inserted is irrelevant in SQL Server and not able to be determined without a field that includes a datetime or similar that you can use to sort the dataset by insertion time.

You could write a query that assigned a row number arbitrarily to both tables and joined them together on this, but without any common link between the two tables, this is meaningless.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

What determines "Position" of the row in your tables?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 3

mpradeesh (7/20/2010)

There is no primary/foreign key relationships and so no joins please.

:blink: What is this supposed to mean?

Are you trying to say that there is no way to know which row of the first table somehow correlates to which row on the second table?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

mpradeesh SSC Enthusiast Points: 168 More actions · Answer 4

mpradeesh

SSC Enthusiast

Points: 168

July 20, 2010 at 9:58 am

#1195650

Thank you all. I will go will the row_id for each positiion and join it to the both columns

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

mpradeesh (7/20/2010)

Thank you all. I will go will the row_id for each positiion and join it to the both columns

Thanks

Forget table 1 with the ID, it's meaningless and pointless.

If you're looking for a numbered output from Table 2 with the names then do it properly with ROW_NUMBER().

If you're looking to assign ID's to the rows of Table 2 then do it properly with an identity column.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 6

Chris Morris-439714 (7/20/2010)

If you're looking to assign ID's to the rows of Table 2 then do it properly with an identity column.

:pinch: Just wondering why an identity column is the proper way to do it - is it to add a meaningless id to an already ugly design?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.