How Do I Script Table Data In SQL Server
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…
From the next window that opens, select to script the entire database or only a few objects. I opted to script only specific tables.
On the next screen you can define the output path if you are saving the scripts to file. But have a look at the Advanced button. Ever noticed that before? Yeah, well apparently neither did I. Click on that button.
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.