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.

Answer 2

If you need to take an entire database, including schema, objects and data, I find the easiest way is to create a full backup and then restore it elsewhere. SQL Server Management Studio includes lots of different options to generate backups, including options to include users and to script object level permissions etc.. Detailed instructions for creating a backup and restoring from a backup from SQL Server Management Studi are available on MSDN.

If you just want to script insert statements to copy the data somewhere else, I've had success with this stored procedure. There are detailed instructions in the comments at the top of that script (scroll down past the NOTE to the examples). Basically once you've executed the stored procedure once, you can call the proc using a command like:

EXEC sp_generate_inserts 'tableName'

Answer 3

Try this

SELECT 'EXEC sp_generate_inserts ' + 
'[' + name + ']' + 
',@owner = ' + 
'[' + RTRIM(USER_NAME(uid)) + '],' + 
'@ommit_images = 1, @disable_constraints = 1'
FROM sysobjects 
WHERE type = 'U' AND 
OBJECTPROPERTY(id,'ismsshipped') = 0

Obtained from My code library . Just go a bit down in the page and you will find

Hope this helps

Answer 4

SQL Server Management Studio does not have this feature. You need some third party tool.

Eg. RedGate or SQLDumper.

Or you can write your own.

Answer 5

I dealt with this a while ago by writing a script (VBScript) to do it. Handles most data types, including blobs.

Get it from my site.

HTH.

Answer 6

This feature doesn't exist in SQL 2005. However, it was added in SQL 2008, so one solution is to upgrade. You might use the free SQL Express or the low-cost SQL Developer, if they meet your requirements.

Otherwise, you would need to write a program to do it, or use a third-party solution.