XPSCodes

After joining asp.net, one of the common questions I usually come across in forums is, How to copy a table within same database or to a different database. This can be done in many different ways. I’ll list few ways that I know of. All scenarios are explained with an example.

Copy Table With in same database:

If the table needs to be created in the same database, its very easy. Lets assume we have a table SoruceTable in database DB1. To create an identical table DestTable in same database, we can use this query:

Select * Into DestTable From SourceTable

The above query will create an identical table named DestTable similar to SourceTable and will copy all rows from SourceTable to DestTable.

If you want to copy only the table definitions, without any data, just add a where condition to the above query.

Select * Into DestTable From SourceTable

Where 1 = 2

If we need some test data, but not all rows from the SourceTable, add top <count> and an order by clause to the above query. For example, to copy 10 random rows from SourceTable,

Select Top 10 * Into DestTable From SourceTable

Where 1 = 2

Order by NewId()

In the above query, Order By Newid() does the trick. Its taking care of picking random records from SourceTable.

If the DestTable needs to be created on a different database say DB2 on same sql server:

Select * Into [DB2].[dbo].DestTable From SourceTable

or

Select * Into [DB2]..DestTable From SourceTable

This ends the first part of this article. In next part, I’ll explain about copying tables between databases residing on different servers. Hope you read something useful. All comments are most welcome.

written by XPSCodes