Home » Interview Q & A » Q5. How to transfer the whole data from one table to another table which does not exists in database?

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

Our Categories

Blog Stats

  • 34,479 hits

Calendar

June 2013
M T W T F S S
« May    
 12
3456789
10111213141516
17181920212223
24252627282930

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: