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]
GO

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

Written by: Mohammed Rashid
         http://BlogUnlimited.com/MohammedRashid

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) = ''
)
AS
BEGIN

 --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')
 FROM INFORMATION_SCHEMA.TABLES
 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
  BEGIN
   SET @sql = 'SELECT TOP 1 ''SET IDENTITY_INSERT ' + @tableName + ' ON '' FROM ' + @tableName
   EXEC sp_executesql @sql
  END

 --Prepare list of columns and values

 --To get the first column's ID
 SELECT @Column_ID = MIN(ORDINAL_POSITION)  
 FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
 WHERE  TABLE_NAME = @tableName

 --Loop through all the columns of the table, to get the column names and their data types
 WHILE @Column_ID IS NOT NULL
  BEGIN
   SELECT  @Column_Name = QUOTENAME(COLUMN_NAME),
   @Data_Type = DATA_TYPE
   FROM  INFORMATION_SCHEMA.COLUMNS (NOLOCK)
   WHERE  ORDINAL_POSITION = @Column_ID AND
   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  +
   CASE
    WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')
     THEN
      'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @Data_Type IN ('datetime','smalldatetime')
     THEN
      'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'
    WHEN @Data_Type IN ('uniqueidentifier')
     THEN 
      'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'
    WHEN @Data_Type IN ('text','ntext')
     THEN 
      'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'     
    WHEN @Data_Type IN ('binary','varbinary')
     THEN 
      'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')' 
    WHEN @Data_Type IN ('timestamp','rowversion')
     THEN 
      'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
    WHEN @Data_Type IN ('float','real','money','smallmoney')
     THEN
      'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ',2)' + ')),''NULL'')'
    ELSE
     'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ')' + ')),''NULL'')'
   END   + '+' +  ''',''' + ' + '
   
   --Generating the column list for the INSERT statement
   SET @columnList = @columnList +  @Column_Name + ',' 

   SELECT  @Column_ID = MIN(ORDINAL_POSITION)
   FROM  INFORMATION_SCHEMA.COLUMNS (NOLOCK)
   WHERE  TABLE_NAME = @tableName AND
   ORDINAL_POSITION > @Column_ID

  END
 --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
  BEGIN
   SET @sql = 'SELECT TOP 1 ''SET IDENTITY_INSERT ' + @tableName + ' OFF '' FROM ' + @tableName
   EXEC sp_executesql @sql
  END

END
GO

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



  sp_Generate_Inserts.sql.txt

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

Print

Advertisements at BlogUnlimited.com

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.
-Anish

# re: Generate INSERT Statements For SQL

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

# 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 .

http://sql-server-script.blogspot.in/

# 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 7 and 7 and type the answer here:

 

Live Comment Preview:

 
«August»
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
31123456

BlogUnlimited.com