Monday, January 27, 2014

Can you script table data, not schema but actual data as insert statements, directly from native SQL Server Management Studio? (Tip, yes)

Dirk Strauss - Script Table Data In SQL Server Management Studio

Script Table Data – I have used other tools to do this for ages. I can’t believe that I never knew that it existed in SQL Server Management Studio. Well ok, in my defence it isn’t quite the most ‘In-your-face’ setting. Nevertheless, it is very convenient and best of all, you don’t need to leave the IDE to script table data. So where is this function? Why, under Tasks of course.

Script Table Data With Generate Scripts

Well, I guess we all know and have used the Generate Scripts function in SQL Server Management Studio before. But with this, you can do so much more in fact. So right click on your Database and select Tasks -> Generate Scripts…

...

Well the option to script table data still isn’t screaming at you from this screen either. Under the General section, select the last option ‘Types of data to script’ and choose one of the options from the dropdown menu. For my purposes, I only want the data in the table so I selected ‘Data Only’. You might want the schema and data, but the important thing is that you have a choice

...

After you click ok, you can choose to script all the objects to one single file or split them up into a separate file for each object. This is obviously a decision you need to make according to your preference. I prefer to have a script per object. So yeah, that’s it! As I said, I never knew this option existed until I went exploring around the settings a bit. Any comments (nice ones) are always welcome.

image

Not like this option/feature wasn't burred deep enough or anything... :/

I wonder how long this has been around (probably knowing my luck, like the better part of a decade..lol )

No comments: