How to transfer the whole data from one table to another table which does not exists in database?


We can do it using SELECT INTO clause.


SELECT <Columns List>

INTO <New Table Name>

FROM <Existing Table Name>



FROM dbo.Employee

When you execute the above query, dbo.EmployeeBKP table will be created automatically with the same structure as dbo.Employee and all the records from dbo.Employee table will be copied to dbo.EmployeeBKP. This method will not work when the destination table already exists.

                    This is the most efficient method and it is faster since it is minimally logged(data is written only to the data pages but not to log file). But there are some drawbacks behind this :( When we transfer the data by using SELECT INTO, the Primary keys, Foreign keys, Constraints and Indexes exist in source table will not be copied to the newly generated table. So It is not a good practice to use this while dealing with big projects. You can use this for testing and analyzing the data.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.