Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Stored Procedure Generates INSERT INTO Statements for Transferring Small Amounts of Data
Using the import/export and data transfer methods provided in SQL Server and SQL Server Integration Services (SSIS) can take a lot of time, so they're not very efficient when you have to transfer only small amounts of data. Rather than manually creating the needed INSERT INTO statements, here's a stored procedure that can automatically create them for you.
Tomaz Kastrun
January 17, 2011
3 Min Read
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:
@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
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.
About the Author
You May Also Like
