Create table as on SQL Server

What's the syntax to do the following.

I do it on Oracle all the time!

create table temp as select * from other_table

This creates a new table temp with the same strucure and copies all the data into it.
Or I can do

create table temp as col1,col2,ect from other_table

Thanks...
fox_doggAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
StefaanConnect With a Mentor Commented:
Hi,

You were very close there, the correct Statemet for SQL Server 6.5 would be :

SELECT Col1, Col2, Col3
INTO <newTableName>
FROM <OriginalTableName>

SELECT INTO is a two-step operation.  

The first step creates the new table, so you have to make sure that the user who is executing this statement has the CREATE TABLE permission in the destination database.

The second step inserts the specified rows into the new table. If the second step fails for any reason (hardware failure, exceeding a system resource, and so on), the new table will exist but have no rows.

If you just want to create a new table with the same table definition, but with no records in it, you could use the same statement, and append a WHERE clausule which will always be false.  This way the first step will create the table, but no records will be inserted.

I think that this is what you are looking for.

Greetings and best regards,


Stefaan Lesage

0
 
scarlettCommented:
Put a # in front of the new table name to make it a temporary table, i.e., #temp_table
0
 
fox_doggAuthor Commented:
Thanks perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.