I have a database with a table Customers that have some data

I have another database in the office that everything is the same, but my table Customers is empty

How can I create a sql file in SQL Server 2005 (T-SQL) that takes everything on the table Customers from the first database, creates a, let's say, buildcustomers.sql, I zip that file, copy it across the network, execute it in my SQL Server and voila! my table Customers is full

How can I do the same for a whole database?

+1  A: 

You could always export the data from the Customers table to an Excel file and import that data into your Customers table.

To import/export data:

  1. Right click on database
  2. Go to Tasks
  3. Go to Import Data or Export Data
  4. Change the data source to Microsoft Excel
  5. Follow the wizard
Bryan Roth
+6  A: 

There was a question about this yesterday, and the best solution was the Database Publishing Wizard.

Forgotten Semicolon
+1  A: 

If you're using Visual Studio 2008 Team Edition for Database Professionals, this can be done with a simple wizard:


Backup and Restore work well for this, as well as detach and attach database.


If both databases resides in the same instance of SQL Server, ie use same connection, this SQL might be helpful:

INSERT INTO [DestinationDB].[schema].[table] ([column])
SELECT [column] FROM [OriginDB].[schema].[table]
+2  A: 

bcp (from the command line) to a networked file and then restore it.


bcp "SELECT * FROM CustomerTable" queryout "c:\temp\CustomerTable.bcp" -N -S SOURCESERVERNAME -T 

bcp TargetDatabaseTable in "c:\temp\CustomerTable.bcp" -N -S TARGETSERVERNAME -T
  • -N use native types
  • -T use the trusted connection
  • -S ServerName

Very quick and easy to embed within code. (I've built a database backup(restore) system around this very command.

oh this is perfect. scriptable, flexible, well-supported and very fast. thanks!
+1  A: 

Another way would be to use Redgate's excellent SqlCompare tool

+12  A: 
The Matt
great answer - thanks
is this functionality available via command line?