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.
Add a comment
|
4 Answers
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 itEquinoX– EquinoX2011-03-06 16:08:47 +00:00Commented Mar 6, 2011 at 16:08
-
@DaniSQL included a screenshot of the correct option.mrdenny– mrdenny2011-03-07 08:45:02 +00:00Commented Mar 7, 2011 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.
-
does this SQL script work if I want to execute it in a oracle db?EquinoX– EquinoX2011-03-06 17:15:29 +00:00Commented Mar 6, 2011 at 17:15
-
not sure. why dont you try it if you have oracle test/dev environment?DaniSQL– DaniSQL2011-03-06 17:44:13 +00:00Commented Mar 6, 2011 at 17:44
-
1The 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– mrdenny2011-03-07 08:44:38 +00:00Commented Mar 7, 2011 at 8:44
-
weird... I don't have that options with meEquinoX– EquinoX2011-03-07 15:31:13 +00:00Commented Mar 7, 2011 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– Jason Cumberland2011-07-22 21:17:12 +00:00Commented Jul 22, 2011 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.
-
1This link is dead.Michael Hampton– Michael Hampton2012-10-13 23:33:32 +00:00Commented Oct 13, 2012 at 23:33