Thread: horizontal union operation

Add a new AutoNumber column to both these tables and use it to join the tables.

I have two irrelavent Microsoft Access tables. They have the same number of records but no other relations. Is there a way to create a new table using SQL with fields from the two source tables put together? For example: Table1 : Name Address --------------------- John 302 ABC Street Smith 412 DEF Street Table2 : Phone email ---------------------- 123 - 4567 m1@hotmail.com 234 - 5678 s1@gmail.com Can I use a simple SQL command to create:...

I'm wondering what you'd do if you had to 'resort to writing a custom program?' - since as you say, they are (I'll use the terms) disjoint/un-related instead of your 'irrelavent' since you dont have anything in common between the tables, and as you say, you cant join by record numbers the only way I can see of doing this (which assumes you can see record numbers) is to manually create a 'link'...

Hi, if everything else fails, you could add a field to both tables ("ID") and fill it with sequential numbers. Then join both tables on the new field.

Try this declare @tbl1 table(name varchar(20), address varchar(50)) insert into @tbl1 select 'John','302 ABC Street' union all select 'Smith','412 DEF Street' declare @tbl2 table(phone varchar(20), email varchar(50)) insert into @tbl2 select '123-4567','m1@hotmail.com' union all select '234-5678','s1@gmail.com' ;with cte1 as ( select t1.*, ROW_NUMBER() over (...

First of all, thanks for all who replied. I was given those Access tables and was asked to do that "horizontal join". I did not design the database, so I have no control over the design of the tables. For example, there is no primary key in those tables. Had I designed them, I would probably put a rowid field there as the primary key. The person who asked me to do this needed the result table "so that he can load the table in...