这是建立一个存储过程，通过执行这个存储过程，将指定的数据表中的数据导成Insert语句形式 无 CREATE PROCEDURE #usp_GenInsertSql (@tablename VARCHAR(256))AS BEGIN DECLARE @sql VARCHAR(MAX) DECLARE @sqlValues VARCHAR(MAX) SET @sql =' (' SET @sqlVa
这是建立一个存储过程，通过执行这个存储过程，将指定的数据表中的数据导成Insert语句形式
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)
END
go
EXEC #usp_GenInsertSql
test 
