18

How can I create clone copy of table without data? Because I just want to copy a definition of table not data.

I tried below but it is copying data as well

Select * 
into Clone_Supplier  
from Supplier
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sql Learner
  • 195
  • 1
  • 1
  • 5
  • I'm seeing you already accepted an answer. Your question doesn't make it clear whether you want the whole definition of the table, including constraints (primary key, defaults, foreign keys, checks, ...), indexes, triggers on the table, and so on. Note that the answer you accepted will not create any constraints, won't have any indexes and won't have any of the triggers that applied to the original table. – TT. Dec 03 '17 at 11:43
  • This looks like a duplication to this: https://stackoverflow.com/q/2505728/9294131 Select Top 0 * into NewTable from OldTable – Avjol Sakaj Aug 06 '20 at 11:41
  • Does this answer your question? [Create table (structure) from existing table](https://stackoverflow.com/questions/2505728/create-table-structure-from-existing-table) – Avjol Sakaj Aug 06 '20 at 11:43

6 Answers6

40

Copy all columns from selected table

Select Top 0 * into NewTable from OldTable

Copy some columns from selected table

Select Top 0 Col1,Col2 into NewTable from OldTable

Copy all(Data and Structure)

Select * into NewTable from OldTable
nazmul.3026
  • 918
  • 1
  • 9
  • 20
  • how can I clone only with structure? – Senior PHP Developer Aug 16 '19 at 04:52
  • 1
    You can Try this to clone with structure. CREATE TABLE NewTable AS (SELECT * FROM OldTable WHERE 1=2); – nazmul.3026 Aug 19 '19 at 03:54
  • No, it's not working : CREATE TABLE [test].[dbo].[users] AS (SELECT * FROM [store_dev].[dbo].[users] WHERE 1=2); – Senior PHP Developer Aug 19 '19 at 04:14
  • If previous one does not work then please try this hope this will work. Thanks Select * into NewTable from OldTable WHERE 1=2 – nazmul.3026 Aug 21 '19 at 06:15
  • but it does not copy the entire structure of a database with indexes and constraints, I already tried that anyways I resolved that problem. Ref: https://dba.stackexchange.com/questions/244973/how-can-i-clone-a-database-in-microsoft-sql-server-using-php/245206?noredirect=1#comment484224_245206 – Senior PHP Developer Aug 21 '19 at 09:24
  • I love a good, simple solution using plain SQL that I never would have thought of. I did not realize "TOP 0" would be valid, but this is the perfect use case for it. Gonna save me a ton of time, thanks! – 4AM Oct 19 '20 at 14:01
7

You could add WHERE 1=2 to get structure only:

Select * 
into Clone_Supplier 
from Supplier
where 1=2;

It won't be exact copy though:

  • no constraints
  • no indexes
  • no triggers
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
4

It is useful table to generate and new table 

 Select Top 0 * into tblNew from tblOld
K M ALIM UDDIN
  • 41
  • 3
2

Solution Query :

SELECT TOP 0 * INTO newTable1 FROM oldTable1;
Lucifer Rodstark
  • 206
  • 4
  • 14
1

You just need to add one false condition. So that it wont return any data and you will create clone copy of table without data. you can use below query

Select * into Clone_Supplier from Supplier WHERE 1=2
Mova
  • 928
  • 1
  • 6
  • 23
-1

Create Table new_table LIKE old_table

Will create an empty copy of original table with original attributes etc.

George Waddell
  • 1