merging data from 3 tables into one table only

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

General SQL Server Forums

New to SQL Server Programming

merging data from 3 tables into one table only

Author

Topic

Lizzie_gurl
Starting Member

11 Posts

Posted - 2011-08-29 : 16:16:59

Hi,

i'm just new in SQL programming and would like to ask for help.
Basically, the task is to merge all the data from 3 tables into just one table. The 3 tables all have the same table structure including the fields and data types.

So for example:
table_1 as the first table
table_2 as the second table
table_3 as the third table

Then all data from table_1, table_2 and table_3 should be imported to one single table called "table_all".

Hope my statement make sense and really hoping you can help me figure this out.

Thanks in advance.

Lizzie :-)

Lizzie :-)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2011-08-29 : 16:38:01

select * from Table1
union
select * from Table2
union
select * from Table3

...but enumerate your column names, and look up the difference between UNION and UNION ALL before implementing this.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-29 : 16:38:34

you can use UNION or UNION ALL, for example:


insert into table_all
select * from table_1
union all
select * from table_2
union all
select * from table_3

That will insert any duplicate rows that may exist in the various tables. If you want only unique rows, replace the two instances of UNION ALL with UNION. (That will remove any duplicates even if they are in the same source table).

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2011-08-29 : 17:05:06

Another handy trick:

select 1 as Source, * from Table1
union all
select 2 as Source, * from Table2
union all
select 3 as Source, * from Table3

...and now you can tell which table each record came from. Useful for debugging.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Lizzie_gurl
Starting Member

11 Posts

Posted - 2011-08-29 : 17:13:55

thank you guys so much for all of your inputs
it works just what i wanted it :-)
really appreciate your help

Lizzie :-)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-29 : 17:15:07

quote:
Originally posted by blindman

Another handy trick:

select 1 as Source, * from Table1
union all
select 2 as Source, * from Table2
union all
select 3 as Source, * from Table3

...and now you can tell which table each record came from. Useful for debugging.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Once I took this one step further and changed the UNION ALL to UNION to make sure I get only unique records, AND to find where they came from. I was so proud of my innovation, but unfortunately it didn't work. After glaring at the screen and blaming Microsoft for their buggy software for a few minutes, I figured it out myself, and then kicked myself!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2011-08-31 : 13:36:07

"An expert is a person who has made all the mistakes that can be made in a very narrow field." --Niels Bohr

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources