生成表insert into语句的存储过程

来源：互联网发布：淘宝宝贝失效还能买吗编辑：程序博客网时间：2024/06/06 03:49
CREATE PROC [dbo].[proc_insert]    (      @tablename VARCHAR(256) ,      @where NVARCHAR(MAX) = ''    )AS    BEGIN        SET nocount ON        DECLARE @sqlstr VARCHAR(4000)        DECLARE @sqlstr1 VARCHAR(4000)        DECLARE @sqlstr2 VARCHAR(4000)        SELECT  @sqlstr = 'select ''insert ' + @tablename        SELECT  @sqlstr1 = ''        SELECT  @sqlstr2 = ' ('        SELECT  @sqlstr1 = ' values ( ''+'        SELECT  @sqlstr1 = @sqlstr1 + col + '+'',''+' ,                @sqlstr2 = @sqlstr2 + name + ','        FROM    ( SELECT    CASE -- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'                                 WHEN a.xtype = 127                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + 'convert(varchar(20),' + a.name + ')'                                      + ' end'                                 WHEN a.xtype = 104                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + 'convert(varchar(1),' + a.name + ')'                                      + ' end'                                 WHEN a.xtype = 175                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + '''''''''+' + 'replace(' + a.name                                      + ','''''''','''''''''''')'                                      + '+''''''''' + ' end'                                 WHEN a.xtype = 61                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + '''''''''+' + 'convert(varchar(23),'                                      + a.name + ',121)' + '+'''''''''                                      + ' end'                                 WHEN a.xtype = 106                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + 'convert(varchar('                                      + CONVERT(VARCHAR(4), a.xprec + 2)                                      + '),' + a.name + ')' + ' end'                                 WHEN a.xtype = 62                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + 'convert(varchar(23),' + a.name                                      + ',2)' + ' end'                                 WHEN a.xtype = 56                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + 'convert(varchar(11),' + a.name + ')'                                      + ' end'                                 WHEN a.xtype = 60                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + 'convert(varchar(22),' + a.name + ')'                                      + ' end'                                 WHEN a.xtype = 239                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + '''''''''+' + 'replace(' + a.name                                      + ','''''''','''''''''''')'                                      + '+''''''''' + ' end'                                 WHEN a.xtype = 108                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + 'convert(varchar('                                      + CONVERT(VARCHAR(4), a.xprec + 2)                                      + '),' + a.name + ')' + ' end'                                 WHEN a.xtype = 231                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + '''''''''+' + 'replace(' + a.name                                      + ','''''''','''''''''''')'                                      + '+''''''''' + ' end'                                 WHEN a.xtype = 59                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + 'convert(varchar(23),' + a.name                                      + ',2)' + ' end'                                 WHEN a.xtype = 58                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + '''''''''+' + 'convert(varchar(23),'                                      + a.name + ',121)' + '+'''''''''                                      + ' end'                                 WHEN a.xtype = 52                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + 'convert(varchar(12),' + a.name + ')'                                      + ' end'                                 WHEN a.xtype = 122                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + 'convert(varchar(22),' + a.name + ')'                                      + ' end'                                 WHEN a.xtype = 48                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + 'convert(varchar(6),' + a.name + ')'                                      + ' end'-- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'                                 WHEN a.xtype = 167                                 THEN 'case when ' + a.name                                      + ' is null then ''NULL'' else '                                      + '''''''''+' + 'replace(' + a.name                                      + ','''''''','''''''''''')'                                      + '+''''''''' + ' 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         IF @where <> ''            SET @sqlstr += ' where ' + @where-- print @sqlstr        EXEC( @sqlstr)        SET nocount OFF    END
0 0