Creating a new table out of 4 Tables

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

Creating a new table out of 4 Tables

Author

Topic

Goku
Starting Member

4 Posts

Posted - 2009-06-17 : 05:06:52

Hi all, how do one create a new table out of 4 identical tables?
Ive tried the following statement:

Select *
into HistoricalRecords.tblRecords
from dbo.EngCat328,dbo.EngCAT9,dbo.MinCAT328,dbo.MinCAT9

but the following error msg are displayed:

Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'ID' in table 'tblRecords' is specified more than once.

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-06-17 : 05:10:55

Hi,
what exactly do you want to do?

Do you want to insert the information from the four table in to HistoricalRecords.tblRecrds?
If they are identical, one table is enough, right?

I do not follow you completely. Please explain what your expected result is.

Best regards,
KFluffie

Goku
Starting Member

4 Posts

Posted - 2009-06-17 : 05:23:21

Hi Kfluffie, these 4 tables have all got identical column names, the only diffrence is the data in each table is diffrent. Now I need to combine the data of all 4 the tables into a new table called tblRecords.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-17 : 06:02:49

The syntax is kinda messed up. Here's what you can do:


Select *
into HistoricalRecords.dbo.tblRecords
from (
  SELECT * FROM dbo.EngCat328 
  UNION ALL
  SELECT * FROM dbo.EngCAT9
  UNION ALL
  SELECT * FROM dbo.MinCAT328
  UNION ALL
  SELECT * FROM dbo.MinCAT9
  ) AS a

- Lumbago

Goku
Starting Member

4 Posts

Posted - 2009-06-17 : 06:32:50

Thank you very much Lumbago, it worked perfectly!
Sorry for the messed up syntax, still learning.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-17 : 06:42:28

Great that it works for you :) But notice the syntax used here; the "from (select ...) as alias" is called a derived table and can be regarded as a sort of inline view. This technique is incredibly powerful once you get the hang of it.

- Lumbago

Goku
Starting Member

4 Posts

Posted - 2009-06-17 : 06:53:47

Noted, thnx I'll practice!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources