Shortcut: Create INSERT statements for data in tables using SSMS

Over the years, I’ve had a surprising number of questions on how to output all the data in a table as a series of INSERT statements. SQL Server Management Studio has had the ability to do this for a long time. Here’s an example.

In Object Explorer, I’ve expanded the WideWorldImporters database, then expanded Tables. Where people come unstuck is they right-click the table, and look at the scripting options:

But if you use the option to script INSERT to a new query window, you get this:

It’s scripting an INSERT statement, not the data as a set of INSERT statements. Now, I think that option should actually be present here, but the way to get to it, is a bit more roundabout. You need to right-click the database, then choose Tasks, then Generate Scripts.

On the first window, click Next.

On the second screen, choose Select specific database objects, then expand Tables, and pick the table you’re after, then click Next:

On the third screen, click the Advanced button.

In the Advanced Scripting Options window, scroll down to find Types of data to script and note the options:

Choose Data only if that’s all you want, then OK. I’ve then chosen Save to new query window, and Next.

I’ve then reviewed what’s going to happen and clicked Next yet again.

On the final screen, I’ve noted the generation happening, then clicked Finish.

Then the window I was after has appeared:

Clearly I wish this was much simpler to do as it’s a fairly common operation, certainly more common than many of the operations that are on the right-click context menu for a table. It’s also not going to be suitable for large amounts of data but often that’s not what you need to script. And you might then want to use a SQL formatting tool to clean up the output window.

2018-04-12