You are here
Home > How To > Script Table Data In SQL Server Management Studio

Script Table Data In SQL Server Management Studio

Script Table Data

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…

Script Table Data

From the next window that opens, select to script the entire database or only a few objects. I opted to script only specific tables.

Script Table Data

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.

Script Table Data

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.

Script Table Data

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.

Download Microsoft SQL Server 2008 Management Studio Express

Dirk Strauss
Dirk is a Software Developer and Microsoft MVP from South Africa. He loves all things Technology and is slightly addicted to Twitter and Jimi Hendrix. Apart from writing code, he also enjoys writing human readable articles. "I love sharing knowledge and connecting with people from around the world. It's the diversity that makes life so beautiful." Dirk feels very strongly that pizza is simply not complete without Tabasco, that you can never have too much garlic, and that cooking the perfect steak is an art he has yet to master.

Similar Articles

  • Pingback: Azure Databases - Restore Local Database To Azure()

  • theMan

    Dirk, thank you very much for this! I had initially overlooked your article while trying to find a method to create a copy of my DB for testing thinking the article was just for scripting a single table which i was aware of. After trying various methods I glanced back at your article and realized it was for creating a script for the entire DB! Cheers! 🙂

    • Dirk Strauss

      That’s awesome! I’m glad The article helped. Now that I know about this, I use it more often than not to script tables and data. Thanks for the feedback.

      • KiwiBri

        Glad I too just found this!! Helped me a lot..

  • Noa

    Thank you, thank you!

  • Bart

    You make my day Dirk! Thx a lot for save my time.

  • Terry

    This is an excellent article and does exactly what I needed! Thanks very much for writing this! p.s. I think you’re right on pizza not being complete sans tabasco!

    • Lol, excellent! Tabasco lovers unite. Have a great new year Terry.

  • Lindo Loo

    Great article! I spent much time looking for just this feature. Used tool with same concept in DB2, but wansn’t hidden as it is in sql studio management. You made my life easy! Thanks!!!! Happy New Year!

  • Refined

    Great article, but will it be possible to script say out of 300,000 records, just 20,000 records.

    • Yes. Write a SQL query to select the 20,000 records into a new table. Then script that table data into the target database.

  • Azeem Sheikh

    What about cdc tables? Can we generate the script for cdc tables?

    • Off the top of my head, I’m not sure that this would include Change Data Capture Tables just because of the nature of the tables. I might be wrong however. Do they show up as part of the objects in the Generate Scripts wizard?