Generate insert statement with data in sql server

Question

I searched & got this below stored procedure to generate insert statement with data. The problem is this stored procedure can not handle single quotes.

Suppose if there is any single quote in data then the insert statement generates but when execute those insert statements then I get an error. So guide me where to fix in procedure as a result it can handle any single quote or any special character.

Thanks

CREATE PROC InsertGenerator    
(@tableName varchar(100)) as    

--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(3000) --for storing the first half of INSERT statement    
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement    
DECLARE @dataType nvarchar(1000) --data types returned for respective columns    
SET @string='INSERT '+@tableName+'('    
SET @stringData=''    

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 @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'    
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'    
END    
ELSE    
if @dataType in ('text','ntext') --if the datatype is text or something else     
BEGIN    
 SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'    
END    
ELSE    
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly    
BEGIN    
 SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'    
END    
ELSE     
IF @dataType='datetime'    
BEGIN    
 --SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'    
 --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations    
 --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'    
 SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'    
  --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations    
END    
ELSE     
IF @dataType='image'     
BEGIN    
 SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'    
END    
ELSE --presuming the data type is int,bit,numeric,decimal     
BEGIN    
 --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'    
 --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'    
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'    
END    

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

FETCH NEXT FROM cursCol INTO @colName,@dataType    
END    
DECLARE @Query nvarchar(4000)    

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

CLOSE cursCol    
DEALLOCATE cursCol

calling like sp-name 'table name'

InsertGenerator    'mytable'

See e.g. http://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table - answer by Shane Fulmer shows a stored procedure to do this. Not sure about whether it handles single quotes properly. Otherwise search Google! You'll find **tons of links** to explore! — marc_s, Apr 11 '14 at 09:21
You can pass values as parameters to `sp_executesql` instead of concatenating. — Hamlet Hakobyan, Apr 11 '14 at 09:22
hi @marc_s : u said cursor inside in trigger is not good. suppose if some one deleting multiple records then how could i capture those deleted records from trigger without using cursor? can u guide me. thanks — Thomas, Apr 11 '14 at 10:47
@Thomas: you need to use a **set-based** approach with the `Deleted` table, e.g. a `JOIN` or something like that. — marc_s, Apr 11 '14 at 10:52
What is the construction `SELECT .... VALUES .... FROM Table`? — Hamlet Hakobyan, Apr 11 '14 at 11:14

score 0 · Accepted Answer · answered Apr 11 '14 at 11:03

What are you trying to accomplish? If you need to move your data to a different db on regular basis I think you would better look into exporting you data to a file and then importing it. Or just use a simple INSERT .. SELECT statement. If it's just a one-time task I would use management studio to generate insert staments rather than writing a stored procedure for that.

Just right click the db. Navigate to Tasks->Generate Scripts.... Select the table you want to generate inserts for. Then on Set Scripting Options tab hit Advaced button and set Types of data to script property to Data only. This will generate inserts statements handling all tricky things such as escaping single quotes.

Hope it helps!

i was looking for full proof store proc which can handle single quote in data and any other special character. — Thomas, Apr 11 '14 at 12:52

Generate insert statement with data in sql server

1 Answers1

Linked