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.
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.
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.