Concatenating multiple table into one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Concatenating multiple table into one

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-05-04 : 14:48:06

I have 3 tables, two have similar columns and data types and one has few columns common to all three and few columns specific to this table.

I want to

select * into D
from A
union all
B
union all
C

i know the above syntax wont work but that is what i want to accomplish

The second thins is to have a column which in the final table that would indicate the sources of the record. for eg:) column called SOURCE and would say A, B or C.

I want to keep all records.

i would appreciate your assitance
Thanks

JKR

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 14:53:11

CREATE TABLE D, with your Source column first, then

INSERT INTO D
select *,'A'
from A
union all
SELECT *,'B'
FROM B
union all
SELECT *,'C'
FROM C

Jim

Everyday I learn something that somebody else already knew

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-05-04 : 14:57:09

select * into ALL
from A
union all
select * from B
union all
select * from C

more like above but C is not the same as Aand B

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-05-04 : 14:59:01

quote:
Originally posted by jimf

CREATE TABLE D, with your Source column first, then

INSERT INTO D
select *,'A'
from A
union all
SELECT *,'B'
FROM B
union all
SELECT *,'C'
FROM C

Jim

Everyday I learn something that somebody else already knew

Thanks Jim . Would it work for C as well cos some of the variables in C are different than Aand B

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 15:03:04

When you are UNION ing all the results have to have the same number of columns and compatible data types. So if Table D has 5 column, but table a has only columns 1,2 and 4 you'd, B has col2 a,2,3 you'd write
SELECT col1,col2,null,col4,'A'
FROM
UNION ALL
SELECT col1,col2,col3,null,B'
FROM B

etc.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources