Generating INSERT statements in SQL Server
This is an alternative for "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