find the trick to copy table structure from another existing table.
Introduction
In some situations we need to create table from another table for many reasons, for example, We need to make some changes in the table`s schema, and we want to keep the original schema as a backup or we just want a look a like table, where we can put some data as backup in future etc.
Of course to copy a table`s structure you must have an existing table, so let us create a table and put some data.
Create Table BaseTable (Id Int , Name Varchar(50), CreatedOn Datetime )
Insert into BaseTable Values (1 , 'Neeraj Prasad Sharma' , GETDATE())
Insert into BaseTable Values (2 , 'Vikas Kumar' , GETDATE())
Insert into BaseTable Values (2 , 'Mark Wizz' , GETDATE())
We have a table with some data, now copy columns from base table using INTO syntax:
Select * INTO CopyTable From BaseTable where 1=2
Note the where condition, we just pass a false statement, can pass any false statement here like 'a'='b' or 2=0 etc.
Now let`s check the existence of our newly created copy table.
Select INTO command will copy table from some other table with its Datatype, but it doesn`t copy any Constraint (primary key, foreign key etc.) except the Not Null constraint.