Stored Procedure Generates INSERT INTO Statements for Transferring Small Amounts of Data

In my job, I often write queries, which I need to send to peers. I like to include small test tables containing sample data so that my peers can try running the queries. The sample data is typically taken from much larger tables.

Related: Using the INSERT Statement

Creating the test tables using the import/export and data transfer methods provided in SQL Server and SQL Server Integration Services (SSIS) was taking a lot of time. So, I wrote SP_GenerateInsertIntoStatement. This stored procedure automatically generates INSERT INTO statements such as

INSERT INTO table  (\[columns1\],\[column2\])   VALUES ('col1','col2')

With these INSERT INTO statements, small amounts of data can be quickly transferred from an existing table to a new one.

SP_GenerateInsertIntoStatement works on SQL Server 2000 and later and is simple to use. The stored procedure takes four input parameters:

For example, if you want to transfer the data from the top 100 rows for all the columns in the Sales table, you'd run the stored procedure using the code

Execute  \[dbo\].\[SP_GenerateInsertIntoStatement\]@Table = 'Sales'

If you want to transfer the data from 55 randomly selected rows for the Q1Sales and Q2Sales columns in the Sales table, you'd use the call

Execute  \[dbo\].\[SP_GenerateInsertIntoStatement\]@Table = 'Sales'  ,@SelectList = 'Q1Sales,Q2Sales'  ,@NOfRows = 55  ,@RandomValues = 'Y'

After the stored procedure executes, you'll receive the INSERT INTO statement that will populate the new table with the desired data. However, the stored procedure doesn't generate the code that creates the new table. I didn't have the stored procedure write that code because SQL Server Management Studio (SSMS) already has this functionality. In SSMS, you simply right-click the existing table, select Script Table as, choose Create to, and select New Query Editor Window. SSMS then displays the CREATE TABLE statement for that table in the Query Editor. You just need to change the table's name and add the generated INSERT INTO statement.

You can download the SP_GenerateInsertIntoStatement stored procedure by clicking the Download the Code Here button. The code is fully commented in case you're interested in learning how the stored procedure works.