Hi all,

I have two totally different tables with completely different data fields. Also, there is no common relationship between these two tables. However, I want to pick few data fields from the each table and merge into a new table! Is this even possible? If so how can I do that?

T

artist,

Im not sure if this will help - but you can always do a full outer join on 1=1. However, it is to be noted that it gives you back the cross product i.e., mn rows for tables of m and n rows respectively.

create table #t1
(
	id int
)

create table #t2
(
	name varchar(100)
)

insert #t1 select 1
insert #t1 select 2
insert #t1 select 3

insert #t2 select 'Ram'
insert #t2 select 'Shyam'

select #t1.id,#t2.name from 
#t1 full outer join #t2 on		

SELECT * FROM

(

SELECT <cols> FROM t1

UNION ALL

SELECT <cols> FROM t2

) AS Der WHERE........

Maybe "select into" statement can help you, take a look at first example (even if no join between tables in your case).

(http://technet.microsoft.com/en-us/library/ms188029.aspx)

SELECT *
INTO newTable
FROM table1, table2
WHERE ...;

Hope this helps.

Regards,

Fanny LE GOFF, Refresh IT Solutions, www.refresh-it-solutions.com

• Edited by Fanny LE GOFF 14 minutes ago

Maybe "select into" statement can help you, take a look at first example here.

SELECT *
INTO newTable
FROM table1, table2
WHERE ...;

Hope this helps.

Regards,

Fanny LE GOFF, Refresh IT Solutions

Select * into creates a new table, so that wouldn't work if the OP wants to consolidate data into an existing table.

HTH..

... and merge into a new table!

said OP

Maybe "select into" statement can help you, take a look at first example here.

SELECT *
INTO newTable
FROM table1, table2
WHERE ...;

Hope this helps.

Regards,

Fanny LE GOFF, Refresh IT Solutions

• Edited by Fanny LE GOFF 23 hours 25 minutes ago

First, this is really a TSQL question, not an engine question.

You've really not given us enough information to say exactly what is appropriate, almost anything along these lines is *possible*, but exactly what you need is very unclear.

Uri's solution is probably closest, but you can also rename the fields as you go, and even cast them to the same data types as needed.

Josh

SELECT * 
INTO mynewtable 
FROM
(
SELECT 
t1x as x, cast(t1y as int) as y
FROM t1
UNION ALL
SELECT 
t2x as x, t2y as y
FROM t2
) AS MYUNION;

• Proposed as answer by AlliedDBA 15 hours 34 minutes ago

... and merge into a new table!

said OP

Good catch.......I didnt have my glasses on :(

HTH...

SELECT * FROM

(

SELECT <cols> FROM t1

UNION ALL

SELECT <cols> FROM t2

) AS Der WHERE..

Thanks...this is almost the perfect answer...but what do you mean by "Der"?

SELECT * FROM

(

SELECT datafields

FROM t1

UNION ALL

SELECT datafields FROM t2

) AS Der WHERE t1.something = t2.something

Am I right here? I have to do the entire process in three tiers...almost 45 tables to around 20, then that 20 tables to 1 large table...

BTW, I am not actually creating new tables but more like create new views...the goal is to create one single sql query which we can run everyday to create one single table to pick datasets and run a report from...I hope its kind of clear now :-) any cool ideas to get this? thanks heaps...