Copying Tables from another Database

Friday, 10 August 2007 13:08 by admin

A question has been asked in the MSDN Forums regarding the way to copy a Table from another Database to another Database. Well, my first answer was quite simple, use DTS (Data Transformation Services). And the person that questioned asked me where to find that in SQL Server Express. And then it struck me, there's no DTS in SQL Server Express!

So, I went on to find another way to transfer a Table from a database to another database. Manually, you'll have to do these steps:

  1. Create the Table Structure to the destination Database
  2. Copy the all data from the source Table to the destination Table

I have arrived with this solution:

(Step 1) - Create Table Structure

Right Click your Table and select Script Table As->CREATE TO->New Query Editor Window

Query1

After that, execute the generated query into your destination Database to create the destination Table.

(Step 2) - Copy All Data

To copy all of your data to the destination table just execute a simple INSERT SQL statement like this:

INSERT INTO destDatabase.dbo.TableDestination
SELECT * FROM sourceDatabase.dbo.TableSource

Voila! You now have a copy of your table in your destination Database! Though the downside of this procedure is that your relationships with other Tables will not be generated, which is fine, because some of your Table's dependencies may not be located on your destination Database.

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Categories:  
Actions:   E-mail | Permalink | Comments (2) | Comment RSSRSS comment feed

Comments

August 14. 2007 00:08

Pauldomag - This is certainly very helpful for creating a copy of tables.

Design for MySpace

August 15. 2007 23:08

Hi Paul. We have just established a site which showcases outsourcing and IT professionals in the Philippines. We would like to invite you to submit articles to further advance our cause.
Aside from article postings, we also have the featured contributor corner, which presents the writer, and his sites.
Hoping to hear from you soon!
Thank you.

Jessica Madrazo

Add comment


(Will show your Gravatar icon)  

biuquote
  • Comment
  • Preview
Loading