Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create table as on SQL Server

Posted on 1998-10-29
3
Medium Priority
?
219 Views
Last Modified: 2008-03-03
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...
0
Comment
Question by:fox_dogg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 3

Accepted Solution

by:
Stefaan earned 80 total points
ID: 1090893
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
 

Expert Comment

by:scarlett
ID: 1090894
Put a # in front of the new table name to make it a temporary table, i.e., #temp_table
0
 

Author Comment

by:fox_dogg
ID: 1090895
Thanks perfect!
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question