I was doing some SSIS development for a client and had to generate and alter some test data for some packages.
I already had a table with values in that I wanted to capture via INSERT statements, so that I could chop and change the values as needed.
There is a method in SQL Management Studio 2008 (SSMS) that allows you to create these statements right out of the box for SQL Server 2008, without using any scripts or add-ins from SSMS.
So, for example, to create the INSERT statements for the Table: HumanResources.Department in the AdventureWorks database. (Note: Click on images to enlarge)
- Chosen Table
Right Click on the Database Name, select Tasks|Generate Scripts…
Click the Next button on the “Generate and Publish Scripts” dialog box that appears.
From the “Choose Objects” screen, select the table from drop down menu. We are doing this for a single table, but as you can see you can do this for multiple objects or the entire database.
Click Next.
On the next screen, decide where you wish to output the script to. I chose a “new query window” in my example.
Then Press the Advanced Button.
From the Advanced Scripting Options dialog, select the Drop-down options for the “Types of data to script” option. You can choose (i) Data Only (ii) Schema and data or (iii) Schema only
Click OK, and then select the Next Button. Click the Next button again from the next screen and then the Finish button after the task has completed
In this example, the CREATE TABLE and INSERT statements appear for my chosen database table within a new query window in SSMS.
I can now extract and edit the INSERT statements, as required, to create new or different test data for my SSIS project.
Elon's Blog 