SQL Server 2005 - Export table programmatically (run a .sql file to rebuild it)
This functionality is already built in to Sql Server Management Studio 2008.
Just download the trial and only install the client tools (which shouldn't expire). Use Management Studio 2008 to connect to your 2005 database (its backwards compatible).
- Right click your database
- Choose Tasks > Generate Scripts
- Press Next, select your database again
- On the 'Choose Script Options' screen, there is an option called Script Data which will generate SQL insert statements for all your data.
(Note: for SQL Server Management Studio 2008 R2, the option is called "Types of data to script" and is the last one in the General section. The choices are "data only", "schema and data", and "schema only")
Use 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.
You can check the following article to see how you can do this by using both SQL Server native tools and the third party tools: SQL Server bulk copy and bulk import and export techniques
Disclaimer: I work for ApexSQL as a Support Engineer
Hope this helps