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?

score 71 · Answer 1 · answered Jul 23 '11 at 08:23

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.

answered Jul 23 '11 at 08:23

Mitch Wheat

295,962
43
465
541

3

I don't understand all the answers that say this can't be done. – ta.speot.is Jul 23 '11 at 08:34
2

They are perhaps not aware that it can be done in SSMS alone. I just did it! It works... – Mitch Wheat Jul 23 '11 at 08:34
2

I do it too. That's why I don't understand. This is like SSMS 101 - along with Import/Export Data. – ta.speot.is Jul 23 '11 at 08:56
It depends on which version of SQL Server. In SQL server 2005 it doesn't exist: http://tinypic.com/view.php?pic=23tmceh&s=6 (unless I'm really missing something...) – Alexander Bird Oct 09 '12 at 14:28
1

first line of my answer: "SSMS supports scripting all data as INSERTS in 2k8:". Question is marked as 2008 – Mitch Wheat Oct 09 '12 at 23:36

score 15 · Answer 2 · edited Jan 28 '16 at 23:30

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

Right-click your database.
Select Tasks > Generate Scripts...
Introduction Step. Click Next.
Choose Objects. Select entire database or selected objects. Click Next.
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.
Click OK. Then Next and Finish.

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

score 6 · Accepted Answer · edited Nov 10 '15 at 15:52

6

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.

edited Nov 10 '15 at 15:52

Luke Girvin

13,221
9
64
84

answered Jul 23 '11 at 08:15

marc_s

732,580
175
1,330
1,459

3

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 Jul 23 '11 at 08:38
1

@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? – marc_s Jul 23 '11 at 09:20
This SSMS Toolpack is not free, it is a trial version. – Vishal Jul 25 '14 at 10:29
@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**.* – marc_s Jul 25 '14 at 11:00

Script all data from SQL Server database

3 Answers3

Linked

Related