Select * into - SQL Server Forums

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

Select * into

Author

Topic

anjali66
Starting Member

23 Posts

Posted - 2011-01-07 : 17:29:34

Hello All,
I am trying to
select * into newtable
FROM (

Select col1, col2, col3 from TableA
Union
SELECT col1, col2, col3 from TableB
UNION
SELECT col1, col2, col3 from TableC)

an empty newTable already exits in the database. I am getting an error "Incorrect syntax near (.
I am not sure what am I doing wrong.

Any help will be appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-07 : 17:32:19

Two problems:

1. You'll need to drop newTable first in order to run that as that code will create the table. Otheriwse, use INSERT INTO/SELECT instead.
2. Use this:

select * into newtable
FROM (

Select col1, col2, col3 from TableA
Union
SELECT col1, col2, col3 from TableB
UNION
SELECT col1, col2, col3 from TableC) t

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-07 : 17:33:01

1. The empty new table will be created so if it is already there then delete it.
2. Your derived table needs an alias name. example: (select... union select ... union select ...) AS dt

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-07 : 17:34:02

Sniped by the Goddess...

No, you're never too old to Yak'n'Roll if you're too young to die.

anjali66
Starting Member

23 Posts

Posted - 2011-01-07 : 17:37:44

Thanks Webfred and Tkizer.

I am new to this forum, but is their any way, I can mark the answer as the right answer so that other people can benefit from it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-07 : 17:49:57

Change the subject to add [RESOLVED]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-07 : 17:50:13

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2011-01-07 : 21:36:52

quote:
Originally posted by anjali66

Hello All,
I am trying to
select * into newtable
FROM (

Select col1, col2, col3 from TableA
Union
SELECT col1, col2, col3 from TableB
UNION
SELECT col1, col2, col3 from TableC)

an empty newTable already exits in the database. I am getting an error "Incorrect syntax near (.
I am not sure what am I doing wrong.

Any help will be appreciated.

drop TABLE newtable
go
create table TableA
(
col1 int,
col2 int,
col3 int
)
go

create table TableB
(
col1 int,
col2 int,
col3 int
)
go
create table TableC
(
col1 int,
col2 int,
col3 int
)
go
select * into newtable
FROM (

Select col1, col2, col3 from TableA
Union
SELECT col1, col2, col3 from TableB
UNION
SELECT col1, col2, col3 from TableC
)t --- note the alias

DROP table TableA
GO
DROP table TableB
GO
DROP table TableC
GO
-----------------------
(0 row(s) affected)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-07 : 23:16:12

Isn't that what we put above already?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources