How can I export data from SQL Server?

Question

I need to export database from SQL Server 2005 to SQL scripts (like we can easily do in MySQL). So I want to get generated file with scripts like this

INSERT INTO ... [row 1]
INSERT INTO ... [row 2]
INSERT INTO ... [row 3]
...

Can anybody explain how can I do this step-by-step?

Answer 1

Actually, one of the easist ways to export data from a MSSQL 2005 database is to use the SQL Server Database Publishing Toolkit which is described in length on Scott Guthrie's blog.

In addition, the SQL Database Publishing toolkit was derived from the tools already builtin to SQL 2005. The article Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects walks you through the different steps to script out the items that make up a database.

Another tool derived from the SQL 2005 tools is the Database Publishing Wizard which is a command line tool which scripts out all items of a database.

A final link to read tells how to Use the Database Publishing Wizard to script your table data.

Good luck and hope this helps you.