CODEWITHSUNDEEP
sqlsql-server
Jeff Stock
Jeff Stock

Reputation: 3856

What is the best way to transfer a table or tables from one SQL server to another?

I have been developing in VB.NET and SQL Server 2008 for a while now, but haven't got into live installs yet. In the database system I used be on it had the ability to archive multiple tables into a .dga file, as it was called. I could then restore the .dga file into another database or on another server.

I'm looking for the easiest way to accomplish something similar in SQL Server.

Upvotes: 1

Views: 526

Answers (4)

Brann
Brann

Reputation: 32376

I would go for one of the following :

  • From MS SQL Management Studio, right click on the database / Tasks / Generate scripts

  • From Visual Studio, in the Server Explorer tab, "publish to provider"

Both will launch a wizard allowing you to export the tables you want the way you want (including data or not, creation scripts or not, etc etc.)

Upvotes: 2

Tom H
Tom H

Reputation: 47464

In addition to HLGEM's suggestions, you can look into SSIS if this is an ongoing process.

Upvotes: 0

casperOne
casperOne

Reputation: 74540

If you want to transfer specific tables, then using Data Transformation Services (right click on the database in SQL Server Management studio and select "Import Data" and it will bring the dialog up for it). Of course, this assumes that you have both databases available to you.

If you are comfortable with replacing the database as a whole, you can easily backup the database and then restore it into a new one through SQL Server Management studio (or through calling the appropriate SP).

Upvotes: 4

HLGEM
HLGEM

Reputation: 96590

If you want to move tabless without data, the simpliest thing is to script the tables you want and run the script.

We script all our db changes and commit them to subversion and then run them as part of the deplyment process.

If you want to put the whole database on prod including data (scrub out test records first!), then do a backup and restore onthe other server.

For future changes, wescript all our db changes and commit them to subversion and then run them as part of the deployment process. There also are tools that look at the structural differnces bewteen the two servers and creates scripts. REd-Gate's SQL Compare is really good for this.

Upvotes: 1

Related Questions