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

Please visit SQLVERSITY.COM for more details.

We can do it using SELECT INTO clause.

Syntax:

SELECT <Columns List>

INTO <New Table Name>

FROM <Existing Table Name>

Ex:

SELECT *

INTO dbo.EmployeeBKP

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 you 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, analyzing the data.