G’day,

I been asked a few times lately the best way to generate INSERT statements.

I’d always replied to use SSMS.

I know SSMS has this feature, it’s just not something that I have the need to use on a regular basic. And – as we all know – when you don’t use something regularly you tend to forget where to find it, or – as happens reasonably regularly nowadays – the feature, or tool, gets an update and you find yourself re-learning something you thought that you were already familiar with.

Fortunately, the feature to generate INSERT statements was pretty much where I expected – but it did take a few minutes to find the exact spot, so I thought that I’d mention it here – just in case anybody else is looking too, or you just need a reminder that SSMS is very capable of performing this task.

Simply right click on the database that contains the table – or tables – that you need to generate INSERT statements for and select TASKS

From the resulting menu choose GENERATE SCRIPTS

Then it’s just a simply case of following the Wizard through.

If you’ve not started this Wizard before then you’ll see the Introduction page – you can safely tick “Don’t show this page again”, so you basically don’t see this page again the next time you start the same Wizard.

Then, on the next page, either select either all objects to script – or just the specific one(s) that you want. In my case I’m interested in one particular table.

On the next page select the location where you want the file to go. I’m selecting a new query editor window, but you could select a file location or the clipboard.

Then hit the “Advanced” button

Clicking the Advanced button will show the “Advanced Scripting Options” – scroll down until you find “Types of data to script” and select your choice from the drop down. I’ve chose to script “Schema and data” – but you can also select just Data or only schema.

The next page will give you a summary

The it’s just a case of hitting Next and then your script is generated. Simply hit finish on the next screen

And you should see your script. Save it to a location of your choice.

I hope this was useful.

Have a great day

Cheers

Marty.