How do I create a table based on another table

0 votes

On the basis of the definition of another table, I want to construct a new table.

Coming from Oracle, I would typically do the following:

 CREATE TABLE schema.newtable AS SELECT * FROM schema.oldtable;

I can't seem to be able to do this in SQL Server 2008. Can someone please help me with this?

Sep 11 in Database by Kithuzzz
• 11,640 points
28 views

1 answer to this question.

0 votes

Although CREATE TABLE AS... SELECT does exist in PDW, such syntax does not exist in SQL Server. You can use the following query in SQL Server to create a blank table:

SELECT * INTO schema.newtable FROM schema.oldtable WHERE 1 = 0;

Note that this does not copy any indexes, constraints, triggers, or other data structures; instead, it constructs the same column structure, including any existing IDENTITY columns. 

I hope this helps you.

answered Sep 12 by narikkadan
• 19,680 points

Related Questions In Database

0 votes
0 answers

How do I list all the columns in a table?

For the various popular database systems, how ...READ MORE

Aug 9 in Database by Kithuzzz
• 11,640 points
23 views
0 votes
0 answers

SQL Update from One Table to Another Based on a ID Match

Account and credit card numbers are stored ...READ MORE

Aug 22 in Database by Kithuzzz
• 11,640 points
45 views
0 votes
1 answer

How do I enable Oracle automatically on systemboot?

To enable the database service to start ...READ MORE

answered Oct 5, 2018 in Database by Frankie
• 9,830 points
217 views
0 votes
1 answer

How do i install MongoDB on ubuntu?

Hey @Aryaa, follow these steps: Import Public Key sudo ...READ MORE

answered Mar 15, 2019 in Database by Avinash
319 views
0 votes
1 answer

Rename column SQL Server 2008

Use sp_rename EXEC sp_RENAME 'TableName.PreviousColumnName' , 'NewColumnName', 'COLUMN' See: SQL SERVER ...READ MORE

answered Feb 23 in Database by Vaani
• 7,020 points
49 views
0 votes
0 answers

How to change column datatype in SQL database without losing data?

I have a SQL Server database, and ...READ MORE

Feb 25 in Database by Vaani
• 7,020 points
91 views
0 votes
0 answers

How to update Identity Column in SQL Server?

With 200 records currently, I want to ...READ MORE

Aug 9 in Database by Kithuzzz
• 11,640 points
159 views
0 votes
0 answers

What is the difference between drop table and delete table in SQL Server?

What is the distinction between the following ...READ MORE

Aug 9 in Database by Kithuzzz
• 11,640 points
63 views
0 votes
1 answer
0 votes
1 answer

How do I obtain a Query Execution Plan in SQL Server?

There are several ways to get an ...READ MORE

answered Sep 20 in Database by narikkadan
• 19,680 points
13 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP