Generating INSERT statements in SQL Server

Introduction

The solution provided by Sumit Amir to Generate INSERT statements in SQL Server has been very helpful since it was published. The original script is great for inserting data into a an empty table. When a table already contains data, it has a limitation as no test is performed to establish if a record already exists.

Using the code

The original stored procedure for the "InsertGenerator" is updated for this article to include additional parameters to allow for one or two key columns to be supplied. These columns are used to build a test to check if the record already exists for each INSERT statement

IF EXISTS (SELECT * FROM dbo.sysobjects 
WHERE id = OBJECT_ID(N'[dbo].[InsertGenerator]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[InsertGenerator]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[InsertGenerator]
(
@tableName varchar(100),
@KeyColumn1 varchar(100)='',
@KeyColumn2 varchar(100)=''
)
AS

-- Generating INSERT statements in SQL Server
-- From CodeProject By Sumit Amar | 17 Jan 2005
-- Updated By Imtiaz Lorgat 19 May 2012 
-- to validate if record exists - supports 2 field Unique index

--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR 
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName

OPEN cursCol

DECLARE @string nvarchar(max) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(max) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
DECLARE @FieldVal nvarchar(1000) -- save value for the current field
DECLARE @KeyVal nvarchar(1000) -- save value for the current field
DECLARE @KeyTest0 nvarchar(1000) -- used to test if key exists
DECLARE @KeyTest1 nvarchar(1000) -- used to test if key exists
DECLARE @KeyTest2 nvarchar(1000) -- used to test if key exists

SET @KeyTest0=''

IF @KeyColumn1<>''
SET @KeyTest0='IF not exists (Select * from '+@tableName

SET @KeyTest1=''
SET @KeyTest2=''

SET @string='INSERT '+@tableName+'('
SET @stringData=''
SET @FieldVal=''
SET @KeyVal=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0
begin
    print 'Table '+@tableName+' not found, processing skipped.'
    close curscol
    deallocate curscol
    return
END

WHILE @@FETCH_STATUS=0
BEGIN

IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
    SET @FieldVal=''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
    SET @KeyVal='''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
    SET @stringData=@stringData+@FieldVal
END

ELSE

if @dataType in ('text','ntext','xml') --if the datatype is text or something else 
BEGIN
    SET @FieldVal='''''''''+isnull(cast('+@colName+' as varchar(max)),'''')+'''''',''+'
    SET @stringData=@stringData+@FieldVal
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
    SET @FieldVal='''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
    SET @stringData=@stringData+@FieldVal
END
ELSE 
IF @dataType='datetime'
BEGIN
    SET @FieldVal='''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
    SET @stringData=@stringData+@FieldVal
END
ELSE 
IF @dataType='image' 
BEGIN
    SET @FieldVal='''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
    SET @stringData=@stringData+@FieldVal
END
ELSE --presuming the data type is int,bit,numeric,decimal 
BEGIN
    SET @FieldVal=''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
    SET @KeyVal='''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
    SET @stringData=@stringData+@FieldVal
END

--Build key test
IF @KeyColumn1=@colName
begin
    SET @KeyTest1 = ' WHERE [' + @KeyColumn1 + ']='
    SET @KeyTest1 = @KeyTest1+@KeyVal+']'
end
IF @KeyColumn2=@colName
begin
    SET @KeyTest2 = ' AND [' + @KeyColumn2 + ']='
    SET @KeyTest2 = @KeyTest2+@KeyVal+']'
end

SET @string=@string+'['+@colName+'],'

FETCH NEXT FROM cursCol INTO @colName,@dataType
END

DECLARE @Query nvarchar(max)

-- Build the test string to check if record exists
if @KeyTest0<>''
begin
    if @Keycolumn1<>''
        SET @KeyTest0 = @KeyTest0 + substring(@KeyTest1,0,len(@KeyTest1)-4)
    if @Keycolumn2<>''
    begin
        SET @KeyTest0 = @KeyTest0 + ''''
        SET @KeyTest0 = @KeyTest0 + substring(@KeyTest2,0,len(@KeyTest2)-4)
    end
    SET @KeyTest0 = @KeyTest0 + ''')'

    SET @query ='SELECT '''+substring(@KeyTest0,0,len(@KeyTest0)) + ') ' 
end
else
    SET @query ='SELECT '''+substring(@KeyTest0,0,len(@KeyTest0))

SET @query = @query + substring(@string,0,len(@string)) + ') ' 
SET @query = @query + 'VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName

exec sp_executesql @query

CLOSE cursCol
DEALLOCATE cursCol

GO

Points of Interest

SQL 2008 R2 has a very useful wizard to generate Scripts for Schema creation or Data Inserts.  This revision adds the ability to check if a record exists thereby allowing for data to be merged.

History

• Ver 0.1b added Dec 5, 2003 - Sumit Amir
• Ver 0.1c updated May 19, 2012 - Imtiaz Lorgat