这是建立一个存储过程，通过执行这个存储过程，将指定的数据表中的数据导成Insert语句形式 无 CREATE PROCEDURE #usp_GenInsertSql (@tablename VARCHAR(256))AS BEGIN DECLARE @sql VARCHAR(MAX) DECLARE @sqlValues VARCHAR(MAX) SET @sql =’ (‘ SET @sqlVa
这是建立一个存储过程，通过执行这个存储过程，将指定的数据表中的数据导成Insert语句形式 <style> .CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;} </style> <!—ecms -ecms
- –> <!—ecms -ecms
- $velocityCount
–> <!—ecms -ecms
–>
CREATE PROCEDURE #usp_GenInsertSql (@tablename VARCHAR(256))AS BEGIN DECLARE @sql VARCHAR(MAX) DECLARE @sqlValues VARCHAR(MAX) SET @sql =' (' SET @sqlValues = 'values (''+' SELECT @sqlValues = @sqlValues + cols + ' + '','' + ', @sql = @sql + QUOTENAME(name) + ',' FROM (SELECT CASE WHEN xtype IN ( 48, 52, 56, 59, 60, 62, 104, 106, 108, 122, 127 ) THEN 'case when ' + name + ' is null then ''NULL'' else ' + 'cast(' + name + ' as varchar)' + ' end' WHEN xtype IN ( 58, 61 ) THEN 'case when ' + name + ' is null then ''NULL'' else ' + ''''''''' + ' + 'cast(' + name + ' as varchar)' + '+''''''''' + ' end' WHEN xtype = 167 THEN 'case when ' + name + ' is null then ''NULL'' else ' + ''''''''' + ' + 'replace(' + name + ','''''''','''''''''''')' + '+''''''''' + ' end' WHEN xtype = 231 THEN 'case when ' + name + ' is null then ''NULL'' else ' + '''N'''''' + ' + 'replace(' + name + ','''''''','''''''''''')' + '+''''''''' + ' end' WHEN xtype = 175 THEN 'case when ' + name + ' is null then ''NULL'' else ' + ''''''''' + ' + 'cast(replace(' + name + ','''''''','''''''''''') as Char(' + Cast(length AS VARCHAR) + '))+''''''''' + ' end' WHEN xtype = 239 THEN 'case when ' + name + ' is null then ''NULL'' else ' + '''N'''''' + ' + 'cast(replace(' + name + ','''''''','''''''''''') as Char(' + Cast(length AS VARCHAR) + '))+''''''''' + ' end' ELSE '''NULL''' END AS Cols, name FROM syscolumns WHERE id = Object_id(@tablename)) T SET @sql ='select ''INSERT INTO [' + @tablename + ']' + LEFT(@sql, Len(@sql)-1) + ') ' + LEFT(@sqlValues, Len(@sqlValues)-4) + ')'' from ' + @tablename + ';' EXEC (@sql) ENDgoEXEC #usp_GenInsertSql <i>本文来源gaodai$ma#com搞$代*码网2</i>test
转载请注明原文链接：自动生成INSERT语句的SQL存储过程
