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, 2022 in Database by Kithuzzz
• 38,010 points
588 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, 2022 by narikkadan
• 63,700 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, 2022 in Database by Kithuzzz
• 38,010 points
344 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, 2022 in Database by Kithuzzz
• 38,010 points
976 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
526 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
606 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, 2022 in Database by Vaani
• 7,060 points
440 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, 2022 in Database by Vaani
• 7,060 points
538 views
0 votes
0 answers

How to update Identity Column in SQL Server?

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

Aug 9, 2022 in Database by Kithuzzz
• 38,010 points
1,127 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, 2022 in Database by Kithuzzz
• 38,010 points
578 views
0 votes
1 answer

How can I create a unique constraint on my column (SQL Server 2008 R2)?

The "indexes and keys" dialogue, not the ...READ MORE

answered Sep 20, 2022 in Database by narikkadan
• 63,700 points
641 views
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, 2022 in Database by narikkadan
• 63,700 points
900 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP