In this tutorial, I will show you a quick and easy to create clone of an existing SQL table in a new table. During copying a table, we can also control that whether the whole data should be copied into new table or just the table structure. But when we create clone of a table, that will not copy all the constraints in the new table but it will copy all the structure that table has.
- To create clone of the whole table with all the data in it, use the following query.
SELECT * INTO [abc1] FROM [abc]
- Here [abc] is the existing table whose clone I want to create in a new table [abc1]. Here is the output in SQL Server after running this statement and then selecting the whole data from this new table.
- If you just want to create clone of the structure of existing table in new table, then try the following query. In following query I have tried some to create a table from two different tables, in which I have cloned all the columns of one table and just a single column of second table which I have created in 1st step.
SELECT [abc].*, [abc1].Ename AS [Ename2] INTO [abc2] FROM [abc1], [abc] WHERE 1 = 2
- In above query, what Where clause is doing is, it is telling not to copy any data from table, because 1 can never be equal to 2, so it will not select any row from those tables, and will copy only the structure of tables. Here is the output in SQL Server.
- Before cloning any table in a new table with specified name you must check that there is no other table already existing with that name. For that purpose, you can view my article on How To Check If Specified Table Exists In SQL Server Database.