26

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...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ichorus
  • 4,567
  • 6
  • 38
  • 46
  • I use Visual Studio SQL Server Explorer for this purpose, it allows automated updates of database structure. – Ginden Jan 12 '15 at 09:15

2 Answers2

45

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

Fredou
  • 19,848
  • 10
  • 58
  • 113
24

You can do

SELECT * INTO #MyTable_tmp FROM MyTable

Then modify your MyTable, and copy your data back in. Other approaches I've seen is to create a new table call it Mytable_Tmp (Not a temp table), which will be your new table.

Then copy your data doing any migrations you need. Then you will drop the original table and do a rename on Mytable.

Or you can get one of the many excellant tools that compare databases and generate difference scripts or VSTS DB Edition (Comes with developer) and you can do a diff script from a project file to a DB.

Edit

When you run SELECT * INTO #MyTable FROM MyTable, SQL Server creates a new temporary table called #MyTable that matches each column and data type from your select clause. In this case we are selecting * so it will match MyTable. This only creates the columns it doesn't copy defaults, constraints indexes or anything else.

Bartosz Konieczny
  • 1,985
  • 12
  • 27
JoshBerke
  • 66,142
  • 25
  • 126
  • 164
  • 1
    My question isn't how to put data into an already existing temp table. I am more concerned about creating a temp table that matches the existing table. I have dozens of tables I need to alter and I don't want to have to create the temp tables by hand. – Ichorus Mar 05 '09 at 19:58
  • 1
    And the keys? index? foreign keys? – Helder Gurgel Nov 04 '15 at 14:30
  • This doesn't create the whole schema just the tables and fields. It's a good way to create a quick temp table – JoshBerke Nov 05 '15 at 17:36
  • " that matches each column and data type from your select clause": note that for varchar/nvarchar it doesn't take the length from the source table, it takes the length from the largest result in the query. if a column in a source table is nvarchar(2000) but the longest result for this column is only 10 characters long, it will create as nvarchar(10). – DaFi4 Nov 22 '19 at 09:43