sqlserver 表中记录生成insert，可以加条件，可以生成建表语句
create PROCEDURE [sp_getinsert]
(
@tablename VARCHAR(256) , --如果非默认架构，可以加上架构名 例如：schema1.tablename
@where VARCHAR(1000) = '',
@create BIT =0
)
AS
BEGIN
SET NOCOUNT ON; IF @create=1
EXEC sp_gettext @name=@tablename,@identity=1,@index=2; DECLARE @sqlstr VARCHAR(max)= '';
DECLARE @sqlstr1 VARCHAR(max);
DECLARE @sqlstr2 VARCHAR(max) ,
@HasIdentity BIT; SET @HasIdentity = OBJECTPROPERTY(OBJECT_ID(@tablename),
'TableHasIdentity'); IF ( @HasIdentity = 1 )
SET @sqlstr = @sqlstr + ' select ''SET IDENTITY_INSERT '
+ @tablename + ' ON'' UNION ALL ' + CHAR(10); SELECT @sqlstr = @sqlstr + 'select ''insert ' + @tablename;
SELECT @sqlstr1 = '';
SELECT @sqlstr2 = ' (';
SELECT @sqlstr1 = ' values ( ''+';
SELECT @sqlstr1 = @sqlstr1 + col + '+'',''+',
@sqlstr2 = @sqlstr2 + QUOTENAME(name) + ','
FROM (
SELECT CASE
-- when a.xtype =173 then 'case when '+QUOTENAME(a.name)+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+QUOTENAME(a.name) +')'+' end'
WHEN a.xtype = 127
THEN 'case when ' + QUOTENAME(a.name)+(CASE WHEN collation ='Chinese_PRC_CI_AS' THEN '' ELSE ' collate '+collation COLLATE DATABASE_DEFAULT end)
+ ' is null then ''NULL'' else '
+ 'convert(varchar(20),'
+ QUOTENAME(a.name) + ')' + ' end'
WHEN a.xtype = 104
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ 'convert(varchar(1),'
+ QUOTENAME(a.name) + ')' + ' end'
WHEN a.xtype = 175
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ '''''''''+' + 'replace('
+ QUOTENAME(a.name)
+ ','''''''','''''''''''')'
+ '+''''''''' + ' end'
WHEN a.xtype = 61
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ '''''''''+' + 'convert(varchar(23),'
+ QUOTENAME(a.name) + ',121)'
+ '+''''''''' + ' end'
WHEN a.xtype = 106
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ 'convert(varchar('
+ CONVERT(VARCHAR(4), a.xprec + 2)
+ '),' + QUOTENAME(a.name) + ')'
+ ' end'
WHEN a.xtype = 62
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ 'convert(varchar(23),'
+ QUOTENAME(a.name) + ',2)' + ' end'
WHEN a.xtype = 56
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ 'convert(varchar(11),'
+ QUOTENAME(a.name) + ')' + ' end'
WHEN a.xtype = 60
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ 'convert(varchar(22),'
+ QUOTENAME(a.name) + ')' + ' end'
WHEN a.xtype = 239
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ '''''''''+' + 'replace('
+ QUOTENAME(a.name)
+ ','''''''','''''''''''')'
+ '+''''''''' + ' end'
WHEN a.xtype = 108
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ 'convert(varchar('
+ CONVERT(VARCHAR(4), a.xprec + 2)
+ '),' + QUOTENAME(a.name) + ')'
+ ' end'
WHEN a.xtype = 231
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ '''''''''+' + 'replace('
+ QUOTENAME(a.name)
+ ','''''''','''''''''''')'
+ '+''''''''' + ' end'
WHEN a.xtype = 59
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ 'convert(varchar(23),'
+ QUOTENAME(a.name) + ',2)' + ' end'
WHEN a.xtype = 58
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ '''''''''+' + 'convert(varchar(23),'
+ QUOTENAME(a.name) + ',121)'
+ '+''''''''' + ' end'
WHEN a.xtype = 52
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ 'convert(varchar(12),'
+ QUOTENAME(a.name) + ')' + ' end'
WHEN a.xtype = 122
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ 'convert(varchar(22),'
+ QUOTENAME(a.name) + ')' + ' end'
WHEN a.xtype = 48
THEN 'case when ' + QUOTENAME(a.name)
+ ' is null then ''NULL'' else '
+ 'convert(varchar(6),'
+ QUOTENAME(a.name) + ')' + ' end'
-- when a.xtype =165 then 'case when '+QUOTENAME(a.name)+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+QUOTENAME(a.name) +')'+' end'
WHEN a.xtype = 167
THEN 'case when ' + QUOTENAME(a.name)+(CASE WHEN collation ='Chinese_PRC_CI_AS' THEN '' ELSE ' collate '+collation COLLATE DATABASE_DEFAULT end)
+ ' is null then ''NULL'' else '
+ '''''''''+' + 'replace('
+ QUOTENAME(a.name)+(CASE WHEN collation ='Chinese_PRC_CI_AS' THEN '' ELSE ' collate '+collation COLLATE DATABASE_DEFAULT end)
+ ','''''''','''''''''''')'
+ '+''''''''' + ' end'
ELSE '''NULL'''
END AS col, a.colid, a.name
FROM syscolumns a
WHERE a.id = OBJECT_ID(@tablename)
AND a.xtype <> 189
AND a.xtype <> 34
AND a.xtype <> 35
AND a.xtype <> 36
) t
ORDER BY colid; SELECT @sqlstr = @sqlstr + LEFT(@sqlstr2, LEN(@sqlstr2) - 1) + ') '
+ LEFT(@sqlstr1, LEN(@sqlstr1) - 3) + ')'' from ' + @tablename
+ ( CASE WHEN @where IS NOT NULL
AND LEN(@where) > 0 THEN ' where ' + @where
ELSE ''
END ); IF ( @HasIdentity = 1 )
SET @sqlstr = @sqlstr
+ ' UNION ALL select ''SET IDENTITY_INSERT ' + @tablename
+ ' OFF'' ' + CHAR(10); PRINT @sqlstr;
EXEC( @sqlstr);
SET NOCOUNT OFF;
END;
调用示例：
exec [sp_getinsert] @tablename='tablename',@where='id>5 ',@create=0