Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

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.

share|improve this answer
    
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

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

share|improve this answer
    
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.

share|improve this answer
1  
This link is dead. –  Michael Hampton Oct 13 '12 at 23:33

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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