Click here to Skip to main content
Click here to Skip to main content
Alternative Article

Generating INSERT statements in SQL Server

, 19 May 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

ilorgat

South Africa South Africa
No Biography provided
Follow on   Google+

Comments and Discussions

 
Suggestionfiltering PinmemberMember 207009723-Jun-14 2:30 
QuestionWhat I use... PinmemberRaghu Karupakala30-May-12 8:11 
QuestionPlease check and fix PinmemberWin32nipuh25-May-12 4:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150326.1 | Last Updated 19 May 2012
Article Copyright 2012 by ilorgat
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid