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
-
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.– TT.2017-12-03 11:43:43 +00:00Commented Dec 3, 2017 at 11:43
-
This looks like a duplication to this: stackoverflow.com/q/2505728/9294131 Select Top 0 * into NewTable from OldTableAvjol Sakaj– Avjol Sakaj2020-08-06 11:41:37 +00:00Commented Aug 6, 2020 at 11:41
-
Does this answer your question? Create table (structure) from existing tableAvjol Sakaj– Avjol Sakaj2020-08-06 11:43:03 +00:00Commented Aug 6, 2020 at 11:43
Add a comment
|
6 Answers
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
6 Comments
Kuldeep Singh Potiwal
how can I clone only with structure?
nazmul.3026
You can Try this to clone with structure. CREATE TABLE NewTable AS (SELECT * FROM OldTable WHERE 1=2);
Kuldeep Singh Potiwal
No, it's not working : CREATE TABLE [test].[dbo].[users] AS (SELECT * FROM [store_dev].[dbo].[users] WHERE 1=2);
nazmul.3026
If previous one does not work then please try this hope this will work. Thanks Select * into NewTable from OldTable WHERE 1=2
Kuldeep Singh Potiwal
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: dba.stackexchange.com/questions/244973/…
|
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
4 Comments
Sql Learner
What should i do to copy indexes and identity?
Lukasz Szozda
@SqlLearner You could use Generate script in SQL Server Management Studio and then rename table and dependencies with new name.
Martin Smith
SELECT ... INTO does copy IDENTITYLukasz Szozda
@MartinSmith I stand corrected :) I am not sure why I was thinking about
IDENTITY(int,1,1) like in DEMOIt is useful table to generate and new table
Select Top 0 * into tblNew from tblOld
1 Comment
Sql Learner
Ohh this one is very useful. Thank you :)
Solution Query :
SELECT TOP 0 * INTO newTable1 FROM oldTable1;
Comments
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
Comments
Create Table new_table LIKE old_table
Will create an empty copy of original table with original attributes etc.
1 Comment
Avjol Sakaj
Select Top 0 * into NewTable from OldTable