Create table using select query in SQL Server

Question

Is there a way to create a new table based on a SELECT query in SQL Server ?

I do not want to manually enter all the fields (the table should be auto generated based on the fields in the query).

I already tried :

CREATE TABLE new_table AS (SELECT * FROM ...);

but it did not work.

Mureinik · Accepted Answer · 2014-04-21 12:24:56Z

3

You are using the wrong syntax for SQL Server. Instead, try:

SELECT * INTO new_table FROM old_table

answered Apr 21, 2014 at 12:24

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SQLsavvy Over a year ago

Thanks but in this case there is no "old_table". I want to use a runtime SELECT query instead of the "old_table".

Mureinik Over a year ago

Same principle applies: `SELECT * INTO new_table FROM (/*some query*/) t;

MoMo · Accepted Answer · 2014-04-21 12:36:28Z

3

CREATE TABLE NEW_TABLE
AS
SELECT employee_id, last_name,
FROM
employees

answered Apr 21, 2014 at 12:36

MoMo

312 bronze badges

1 Comment

SQLsavvy Over a year ago

I do not want to manually enter all the fields (the table should be auto generated based on the fields in the query).

mohan111 · Accepted Answer · 2014-04-21 12:25:05Z

1

you can proceed like this

select * into NewTable from  oldtable

answered Apr 21, 2014 at 12:25

mohan111

8,8854 gold badges35 silver badges61 bronze badges

Comments

UnhandledExcepSean · Accepted Answer · 2014-04-21 13:40:11Z

0

I had to alias the columns in the select, but I would have to think your current query aliases the fields if they don't have name.

select
  *
into #tempabc
from (
  Select 'a' [test]
  union
  Select 'b' [test]
) a

select * from #tempabc

edited Apr 21, 2014 at 13:40

answered Apr 21, 2014 at 12:43

UnhandledExcepSean

12.9k4 gold badges38 silver badges54 bronze badges

3 Comments

SQLsavvy Over a year ago

Thanks but both options involve declaring the field names in the Destination Table. Can this be automated ?

UnhandledExcepSean Over a year ago

The first option didn't specify the columns. I had to alias the field in the select. Also, this question has drastically changed with regards to intent.

SQLsavvy Over a year ago

Thanks, I replaced the discrete values with a Select * query and it worked !

Nayeem Mansoori · Accepted Answer · 2014-04-21 12:41:27Z

0

Maybe you could try like this in your query:

create table #temp(@field1 int,@field2 varchar(10))

insert into #temp

       select a,b from tableFrom



select * from #a

then, when you're not using the table again,

drop table #a

edited Apr 21, 2014 at 12:41

answered Apr 21, 2014 at 12:25

Nayeem Mansoori

8312 gold badges16 silver badges43 bronze badges

4 Comments

mohan111 Over a year ago

question is to create table instantly..not about inserting data into created table

Dan Bracuk Over a year ago

@mohan111, your comment also applies to your own answer. Adding "where 1=3" should solve that little issue.

SQLsavvy Over a year ago

Doesn't work...DROP Table CCC; CREATE TABLE CCC AS (SELECT * FROM AAA); gives me the error Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'AS'.

SQLsavvy Over a year ago

The SELECT Query will Join multiple tables. This is the whole reason I need the Table to be created on the fly.

Anto Raja Prakash · Accepted Answer · 2014-04-21 12:34:59Z

-1

SELECT * INTO new_table FROM old_table where 1=2

where condition should be like your query should not return data

answered Apr 21, 2014 at 12:34

Anto Raja Prakash

1,3888 silver badges13 bronze badges

1 Comment

UnhandledExcepSean Over a year ago

This didn't really address the question, but I didn't know you could create an empty table like that. Awesome to know. Thanks!

Collectives™ on Stack Overflow

Create table using select query in SQL Server

6 Answers 6

2 Comments

1 Comment

Comments

3 Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

Comments

3 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related