declare @tablename VARCHAR(256)
set @tablename='MyDB'
DECLARE @sql VARCHAR(8000)
DECLARE @sqlValues VARCHAR(8000)
SET @sql = ' ('
SET @sqlValues = 'values (''+'
SELECT @sqlValues = @sqlValues + cols + ' + '','' + ',
@sql = @sql + '[' + 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 IN ( 167 )
THEN 'case when ' + name
+ ' is null then ''NULL'' else '
+ ''''''''' + ' + 'replace(' + name
+ ','''''''','''''''''''')'
+ '+''''''''' + ' end'
WHEN xtype IN ( 231 )
THEN 'case when ' + name
+ ' is null then ''NULL'' else '
+ '''N'''''' + ' + 'replace(' + name
+ ','''''''','''''''''''')'
+ '+''''''''' + ' end'
WHEN xtype IN ( 175 )
THEN 'case when ' + name
+ ' is null then ''NULL'' else '
+ ''''''''' + ' + 'cast(replace(' + name
+ ','''''''','''''''''''') as Char('
+ CAST(length AS VARCHAR)
+ '))+''''''''' + ' end'
WHEN xtype IN ( 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
-- PRINT @sql
EXEC ( @sql )
本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请
点击举报。
