How do I generate insert statements with Sql server 2012?

Question

I have sql server 2012 express and just installed the latest version of ssms tools from http://www.ssmstoolspack.com/Download

With sql server 2008 express and ssms tools I was able to right click on a table and see an ssms tools option that allowed me to generate insert statements for a table but now I don't see it? Anyone know how I can generate the insert statements for a table?

score 58 · Answer 1 · answered Dec 28 '12 at 12:52

You can script data natively with SSMS 2012 without using plugins such as SSMS Tools Pack, although the functionality is hidden away.

Right click on your database and select Tasks -> Generate Scripts
Click Next to move past the intro screen and onto the next page, then select the objects you want to script. Note that you can script all SQL objects here, not just tables. This is because this wizard allows you to script schema and data. Importantly, schema only is the default, which you'll need to change on the next page. Click Next.
Now you'll be presented with a set of options mainly relating to how the script is going to be output, however there is an unassuming button labelled Advanced. Clicking this button will give you a dialog with a list of options. The last option under the General section is "Types of data to script", which you should change from "Schema only" to "Data only", or "Schema and data".
Finally, decide where you want the script to do and you should be able to generate your INSERT statements.

See this blog post for more details, which is where I found out about this feature: http://raresql.com/2012/09/30/generating-insert-statements-in-sql-server-2012/

Note that unlike SSMS Tools Pack, this will generate an INSERT statement for every single row you are trying to script. If you have a large number of rows that you need to move around then you may want to pursue alternatives. However, for scripting small configuration tables this is perfect.

For scripting from the command line, sqlpubwiz should work. https://sqlhost.codeplex.com/wikipage?title=DPW%20Command%20Line%20Interface — StuartQ, Jul 24 '13 at 17:36
This project is also worth watching https://github.com/nycdotnet/TSqlFlex — StuartQ, May 11 '15 at 16:26

score 1 · Answer 2 · answered Nov 07 '12 at 16:23

1

Try the procedure at this link.

once you install it, just use EXEC sp_generate_inserts MyTable

answered Nov 07 '12 at 16:23

Data Masseur

1,163
7
15

score 0 · Accepted Answer · answered Nov 07 '12 at 16:00

0

Ah, 2012 ssms tools are no longer free, I had to click on a license and then restart sql server.

answered Nov 07 '12 at 16:00

Eric

3,632
2
33
28

How do I generate insert statements with Sql server 2012?

3 Answers3

Linked