Script all data from SQL Server database

Question

I have two databases with equivalent structure and I need to extract data from one of them in form of INSERT statements (generate script to apply it on the other database).

How can I do it using Management Studio?

Answer 1

SSMS supports scripting all data as INSERTS in 2k8:

Right click on a database and select Tasks->Generate Scripts...

Pick only the tables, Click Next.

Click on the Advanced button. Scroll down and select "Types of data to script" == Data Only

Decide whether you want results in a new query window or a file. Click on to Finish.

You then change the using DBname at the top of the script.

Answer 2

You can do it with SQL Server Management Studio. Here are the steps - as Mitch Wheat describes above (for SQL Server Management Studio 2012):

1. Right-click your database.
2. Select Tasks > Generate Scripts...
3. Introduction Step. Click Next.
4. Choose Objects. Select entire database or selected objects. Click Next.
5. Set Scripting Options. Here's the key step. In order to get all data and objects, click the Advanced button. Then in the Advanced Scripting Options, set the value for Types of data to script to Schema and data.
6. Click OK. Then Next and Finish.

You will have a script file that contains both database schema and data.