MS SQL Server with management studio - how to script a table with its data (as insert statements)?

Question

Possible Duplicate:
Get script of SQL Server data

I want to move some rows from 1 table (on one server) to another (on another server).

The table schema is the same, what I need to do is generate some insert statements. Is there a way to do this using some built in / best practice way?

Import / Export seems completely extreme and excessive.

Thanks in advance.

Might help you http://stackoverflow.com/questions/2321052/get-script-of-sql-server-data — Niklas, Feb 29 '12 at 13:29

score 31 · Accepted Answer · answered Feb 29 '12 at 13:35

31

Right-click on the database name and choose Tasks -> Generate Scripts. On the Scripting Options screen set Script Data to true.

answered Feb 29 '12 at 13:35

Anthony Faull

17,549
5
55
73

This is great! I usually do just a select and copy data into Notepad++ and do few replacements using regular expressions. Fast as well, but not as fast as this one is. Great to know. +1 – Robert Koritnik Feb 29 '12 at 13:39
11

**FYI**: I don't know which SQL Server/SSMS version you're using, but you have to set *Advanced* properties and the one that's relevant here is **Type of data to script** where you either select **Data only** or **Schema and data**. That's of course related to SQL Server 2008 R2. – Robert Koritnik Feb 29 '12 at 13:41

score 3 · Answer 2 · answered Feb 29 '12 at 13:32

You can do it with the publish wizard

Maybe it’s well-known, but new to me, you can just run “C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.4\SqlPubWiz.exe”. This nifty little tool can generate INSERT script very easily.

Reference here

MS SQL Server with management studio - how to script a table with its data (as insert statements)?

2 Answers2