I have a database in which I have 5 tables with values in it. Is there a way in SQL server management studio where I can generate a SQL script to re-generate all this table data in a different SQL server machine? So I can just run the SQL script on the other machine and it will work.


Run the script wizard for the table. When you get to the option page one of the options is to script data. Set that to true and finish the wizard. The INSERT statements will be included in the script.

  • I did run the script wizard and tick the script all objects in the selected database, but the generated sql query doesn't have any INSERT in it – EquinoX Mar 6 '11 at 16:08
  • @DaniSQL included a screenshot of the correct option. – mrdenny Mar 7 '11 at 8:45

All tools I can think of of are geared towards definition or data: not both. This includes professional products like Red Gate stuff.

I would suggest using SSMS Tools Pack to generate the INSERT statements (it's free) and SSMS to generate the definition.


In Script Wizard - Choose Script Options make sure you select script data to true to generate table scripts with data. Check details at Script Data in MS SQL Server 2008 Database Tables using Generate SQL Server Script Wizard.

enter image description here

  • does this SQL script work if I want to execute it in a oracle db? – EquinoX Mar 6 '11 at 17:15
  • not sure. why dont you try it if you have oracle test/dev environment? – DaniSQL Mar 6 '11 at 17:44
  • The data insert script should work against oracle. The basic INSERT syntax is the same. The table creation scripts probably won't as the datatype names are different. – mrdenny Mar 7 '11 at 8:44
  • weird... I don't have that options with me – EquinoX Mar 7 '11 at 15:31
  • I use this procedure a lot to generate inserts. Link and syntax are below. vyaskn.tripod.com/code/generate_inserts.txt exec sp_generate_inserts @table_name = table' – Jason Cumberland Jul 22 '11 at 21:17

I saw this post very helpful for generating SQL Insert statements from an existing database table with options to filter and sort the output data. It works for IDENTITY columns also. It allows you to pass as parameters the SQL tableName, whereClause to filter data and orderByClause to order the data the way you want. Hope it may be useful to others as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.