SQL Server 2005 - Export table programatically (run a .sql file to rebuild it)

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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

http://msdn.microsoft.com/en-us/library/aa833411(VS.80).aspx

Answer 4

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

Answer 5

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]
GO

Answer 6

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

e.g.

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.

Answer 7

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