//
You're reading...
SQL

Copy one database table rows to another database table

It is a common practice in IT industry that organizations manages data in different databases for different departments. For example a company has a Quality assurance database and a developer database. But they faces an issue when they need a record from some database to their database. In that case, IT person needs to write an insert query with the help of existing data values. But it won’t be a preferable solution if you have 30 or more columns in your table. I would recommend to use this statement to resolve the issue,

insert into QualityAssuranceDB.dbo.Customers (columnA, ColumnB)
Select columnA, columnB from DeveloperDB.dbo.Customers

Isn’t that too simple ?

But wait for a while, did we miss something ?
Yup, still if we have 30 or more columns then how to write “name of the columns” ? do we need to pick column names one by one ?

No, there is no need. For that the following script will help:

select (name + ',') as TableColumns from sys.columns where object_id = object_id('YourTableName')

Copy the result and paste into query window to represent your table column names. If you want to exclude the column that belongs to identity fields then use the below script:

select (name + ',') as TableColumns from sys.columns where object_id = object_id('YourTableName') and is_identity = 0

I hope its a viable solution to this problem.

NOTE: The instructions will work if the two databases belongs to the same location.

Awaited for your feedback….
Cheers!

Regards,
Shahan

Advertisements

About Shahan Ayyub

Shahan is a Microsoft Certified Professional of .Net. Mostly self taught programmer and a quick learner, who just love to code and always eager to find unique solutions to the common problems. Currently working as an iOS developer.

Discussion

One thought on “Copy one database table rows to another database table

  1. Hey! genius move!

    Posted by Cesar Bonilla | August 30, 2013, 4:21 am

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: