What is the equivalent of 'CREATE TABLE ... LIKE ..." in SQL Server

sql-server tsql alter
Ichorus picture Ichorus · Mar 5, 2009 · Viewed 37.6k times · Source

I am working with SQL Server (I am a SQL Server noob) and trying to alter a table. I want to CREATE TABLE LIKE to safely store data while I drop keys and constraints and all the other rigamorole that SQL Server seems to require when altering on the original table but I have not been able to find a match to that command...

Answer

Fredou picture Fredou · Mar 5, 2009

you want to recreate the same structure?

how about this

 SELECT *
 into test
 FROM myRealTable
 where 0=1

no data will be inserted into the new table

Related questions

How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?

I would like to write a single SQL command to drop multiple columns from a single table in one ALTER TABLE statement. From MSDN's ALTER TABLE documentation... DROP { [CONSTRAINT] constraint_name | COLUMN column_name } Specifies that constraint_name or column_…

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More