用sql server中的数据生成插入语句 [英] Generate insert statement with data in sql server

问题描述

我搜索&得到这个下面的存储过程来生成带有数据的插入语句.问题是这个存储过程不能处理单引号.

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.

谢谢

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'

推荐答案

你想完成什么?如果您需要定期将数据移动到不同的数据库，我认为您最好考虑将数据导出到文件然后导入.或者只是使用一个简单的 INSERT .. SELECT 语句.如果这只是一次性任务，我会使用管理工作室来生成插入语句，而不是为此编写存储过程.

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.

只需右键单击数据库.导航到 Tasks->Generate Scripts....选择要为其生成插入的表.然后在 Set Scripting Options 选项卡上点击 Advanced 按钮并将 Types of data to script 属性设置为 Data only.这将生成处理所有棘手事情的插入语句，例如转义单引号.

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.

希望有帮助！

这篇关于用sql server中的数据生成插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！