27

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?

Improve this question

3 Answers 3

Reset to default
71

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

enter image description here

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I don't understand all the answers that say this can't be done.
They are perhaps not aware that it can be done in SSMS alone. I just did it! It works...
I do it too. That's why I don't understand. This is like SSMS 101 - along with Import/Export Data.
It depends on which version of SQL Server. In SQL server 2005 it doesn't exist: tinypic.com/view.php?pic=23tmceh&s=6 (unless I'm really missing something...)
first line of my answer: "SSMS supports scripting all data as INSERTS in 2k8:". Question is marked as 2008
15

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.

Improve this answer

Comments

7

You could use the free SSMS Toolpack add-in for SQL Server Management Studio.

See the section on Generate Insert statements from resultsets, tables or database

Update: OK, for SSMS Toolpack in SSMS 2012, a licensing scheme has been introduced. SSMS Toolpack for earlier versions of SSMS are however still free.

Improve this answer

4 Comments

The SSMS tools Pack is great, but you don't need it to script out all data as INSERTS; SSMS can do that.
@Mitch Wheat: yes, SSMS itself can do it - but the Toolpack does it better, in my opinion - more straight forward, less detours.... plus, the Toolpack can generate INSERTs based on the results of a query... SSMS itself cannot - right?
This SSMS Toolpack is not free, it is a trial version.
@Vishal: well, to be really precise: Licensing only applies to SSMS 2012 and higher versions. For previous SSMS versions the SSMS Tools Pack is still FREE.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.