| Downloads |
|---|
| 129187.zip |
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.
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
(\\[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:
- @Table. You use this parameter to specify the name of the existing table. This is the only mandatory parameter.
- @SelectList. You use this parameter to specify the columns that you want to transfer from the existing table to a new empty table. If you don't include this parameter, all the columns will be transferred (the default). If you want to specify certain columns, the columns' names must be in a comma-separated list.
- @NofRows. You use this parameter to specify the number of rows you want to transfer. If you don't include this parameter, the first 100 rows will be transferred (the default).
- @RandomValues. You use this parameter when you want to have the rows randomly selected from the existing table instead of taken in logical order. If you don't include this parameter or if you specify 'N', the rows will be taken in logical order (the default). If you specify 'Y', the rows will be selected in random order.
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
\\[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
\\[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.
