Generate INSERT Statements For SQL

Very often we come across situations when we need to copy data from one database to other database. For example, you have a production database where you cannot test your application, but you need the data to test your application in development environment. Another example I can think of is: you have to create a master data script for production environment, from the current development environment. There may be other specific scenarios where you need to copy data from one database to other. If data volume is low, you can probably insert it manually, but it is tedious and error prone. Obviously, if the data is too much, it is very very difficult to achieve this goal. In such situations, an automated way of transerring the data is very useful. Keeping that in mind, I have written a stored procedure that will generate INSERT statements. The stored procedure is parameterized for few conditions which I am explaining below in usage of this stored procedure. But before that have a look at the stored procedure script.

--If stored procedure already exists, drop it
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Generate_Inserts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Generate_Inserts]

Procedure: sp_Generate_Inserts
  (Copyright © 2012 Mohammed Rashid. All rights reserved.)
Purpose: To generate INSERT statements from existing data.

Written by: Mohammed Rashid

Tested on: SQL Server 2005

Date created: 10 March 2012

Example 1: To generate INSERT statements for table 'MyTable':
  EXEC sp_Generate_Inserts 'MyTable'

Example 2: To generate INSERT statements for 'MyTable' table where COL1 > 100 and COL2 contains 'some string':
  NOTE: The user of this SP is assumed to be aware of basic SQL WHERE clause

  EXEC sp_Generate_Inserts 'MyTable', @whereClause = 'COL1 > 100 AND COL2 LIKE ''%some string%'''

Example 3: To generate INSERT statements for 'MyTable' table where the data is sorted by COL1:

  EXEC sp_Generate_Inserts MyTable, @orderByClause = 'ORDER BY COL1'

Example 4: To generate INSERT statements for 'MyTable' table where COL1 > 100 and COL2 contains 'some string' 
   and the data is sorted by COL3 in descending:

  EXEC sp_Generate_Inserts MyTable, @whereClause = 'COL1 > 100 AND COL2 LIKE ''%some string%''', @orderByClause = 'ORDER BY COL3 DESC'

--Create the stored procedure
CREATE PROCEDURE sp_Generate_Inserts
 @tableName nvarchar(100),
 @whereClause nvarchar(MAX) = '',
 @orderByClause nvarchar(MAX) = ''

 --Declare variables
 DECLARE @tableHasIdentity bit
 DECLARE @sql nvarchar(MAX)
 DECLARE @columnList nvarchar(MAX)
 DECLARE @valueList nvarchar(MAX)
 DECLARE @Column_ID int
 DECLARE @Column_Name varchar(128)
 DECLARE @Data_Type varchar(128)

 --Initialize variables
 SET @columnList = ''
 SET @valueList = ''
 SET @Column_ID = 0
 SET @Column_Name = ''

 --Check if the table has an identity column
 SELECT @tableHasIdentity = OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity')
 WHERE TABLE_NAME = @tableName

 --Check if we need 'SET IDENTITY_INSERT tableName ON' statement.
 --If the table has Identity column, the IDENTITY_INSERT will be SET to ON
 IF @tableHasIdentity = 1
   SET @sql = 'SELECT TOP 1 ''SET IDENTITY_INSERT ' + @tableName + ' ON '' FROM ' + @tableName
   EXEC sp_executesql @sql

 --Prepare list of columns and values

 --To get the first column's ID
 WHERE  TABLE_NAME = @tableName

 --Loop through all the columns of the table, to get the column names and their data types
   @Data_Type = DATA_TYPE
   TABLE_NAME = @tableName
   --Determining the data type of the column and depending on the data type,
   --the VALUES part of the INSERT statement is generated.
   SET @valueList = @valueList  +
    WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')
      'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @Data_Type IN ('datetime','smalldatetime')
      'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'
    WHEN @Data_Type IN ('uniqueidentifier')
      'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @Data_Type IN ('text','ntext')
      'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'     
    WHEN @Data_Type IN ('binary','varbinary')
      'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')' 
    WHEN @Data_Type IN ('timestamp','rowversion')
      'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
    WHEN @Data_Type IN ('float','real','money','smallmoney')
      'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ',2)' + ')),''NULL'')'
     'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ')' + ')),''NULL'')'
   END   + '+' +  ''',''' + ' + '
   --Generating the column list for the INSERT statement
   SET @columnList = @columnList +  @Column_Name + ',' 

   WHERE  TABLE_NAME = @tableName AND

 --End of loop

 --Remove extra characters that got concatenated during the last run through the loop
 SET @columnList = LEFT(@columnList,len(@columnList) - 1)
 SET @valueList = LEFT(@valueList,len(@valueList) - 6)

 --Build the SQL statement
 SET @sql = 'SELECT ''INSERT INTO [' + @tableName + '] (' + @columnList + ') ' +
    'VALUES (''+' + @valueList + '+'')'' FROM ' + @tableName

 --Apply filter (WHERE clause) and sorting (ORDER BY clause) if required
 IF LEN(@whereClause) > 0
  SET @sql = @sql + ' WHERE ' + @whereClause
 IF LEN(@orderByClause) > 0
  SET @sql= @sql + ' ORDER BY ' + @orderByClause

 --Execute SQL statement to print INSERT statements
 exec sp_executesql @sql

 --SET IDENTITY_INSERT OFF if it is set ON above
 IF @tableHasIdentity = 1
   SET @sql = 'SELECT TOP 1 ''SET IDENTITY_INSERT ' + @tableName + ' OFF '' FROM ' + @tableName
   EXEC sp_executesql @sql


You can download this stored procedure script from the link given below.


posted @ Saturday, March 10, 2012 10:26 PM


HostAdda - Your Internet Home

Comments on this entry:

# re: Generate INSERT Statements For SQL

Left by Anish Bazmee at 3/22/2012 9:05 AM
Very nice and easy to understand script. It helped me a lot when I was looking for a solution to transfer data from one database to another database. Thank you for posting.

# re: Generate INSERT Statements For SQL

Left by Murali Srinivas at 3/25/2012 8:21 AM
Hi Rashid,
Useful. Nice posting!

# re: Generate INSERT Statements For SQL

Left by Darshan at 8/23/2012 3:01 PM
Very useful. Specially option to generate scripts for few rows with the help of where clause.

# re: Generate INSERT Statements For SQL

Left by Abhishek at 9/13/2012 11:32 AM
Wonderful and ready to use script. Worked without any correction and as per my expectation. You saved my lot of time. Thank you so much.

Abhishek R.

# re: Generate INSERT Statements For SQL

Left by Sharma at 10/6/2012 11:43 AM
Thanks a very nice code shared by you. Thanks.

# re: Generate INSERT Statements For SQL

Left by neeraj prasad at 10/24/2012 10:57 AM
here i made a store procudure for that .

# re: Generate INSERT Statements For SQL

Left by Darshan at 11/2/2012 8:01 AM
This does not work welll with tables having xml columns. For me, it is throwing error
"Msg 6354, Level 16, State 10, Line 1
Target string size is too small to represent the XML instance"

Table Structure:
Column1, int, not null
Column2, varchar(100), not null
Column3, XML, not null

# re: Generate INSERT Statements For SQL

Left by Frederic JOFES at 2/18/2013 5:09 PM
Thanks a lot for this script

# re: Generate INSERT Statements For SQL

Left by DucSon EK at 4/17/2013 1:29 PM
Great !! Thank so much

Your comment:

 (will not be displayed)

Please add 8 and 4 and type the answer here:


Live Comment Preview: