美文网首页
MSSQL存储过程生成insert into 语句

MSSQL存储过程生成insert into 语句

作者: _意义 | 来源:发表于2020-06-11 14:10 被阅读0次
    USE [数据库]
    GO
    
    /****** Object:  StoredProcedure [dbo].[sp_get_InsertSql]    Script Date: 2020/5/20 17:13:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:      <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description: <Description,,>
    -- =============================================
    CREATE PROCEDURE [dbo].[sp_get_InsertSql]
        --@dbName              VARCHAR ( 32)= '' ,    -- 数据库名称
        @tabList          VARCHAR ( max )-- 要导出数据的表名,表名之间用逗号隔开
    AS
    
        DECLARE
    
           @dbName  VARCHAR ( 32),
    
           @IncludeIdentity  BIT = 1,
    
           @index     INT ,
    
           @wi        INT ,
    
           @SQL       VARCHAR ( max ),
    
           @SQL1      VARCHAR ( max ),
    
           @tabName   VARCHAR ( 128) ,
    
           @colName   VARCHAR ( 128),
    
           @colType   VARCHAR ( 128),
    
           @tabPrefix VARCHAR ( 32),
    
           @cols      VARCHAR ( max ),
    
           @colsData  VARCHAR ( max ),
    
           @SQLWhere  VARCHAR ( 1024),       
    
           @SQLIdentityOn    VARCHAR ( MAX ),
    
           @SQLIdentityOff VARCHAR ( MAX );
    
              
        DECLARE @t_tb TABLE ( TB varchar ( 128), Sqlwhere varchar ( 1024), SN BIGINT IDENTITY ( 1, 1))
    
        DECLARE @tb TABLE ( insert_sql VARCHAR ( max ), SN BIGINT IDENTITY ( 1, 1));
    
        DECLARE @colList TABLE ( colName VARCHAR ( 128), colType VARCHAR ( 128),
    
           colValueL VARCHAR ( 120), colValueR VARCHAR ( 120), selColName VARCHAR ( 128));
    
    BEGIN
    
        SET NOCOUNT ON
    
        SET @tabList = REPLACE ( @tabList, CHAR ( 9), '' )
    
        SET @tabList = REPLACE ( @tabList, CHAR ( 10), '' )
    
        SET @tabList = REPLACE ( @tabList, CHAR ( 13), '' )
    
        SET @dbName = LTRIM ( RTRIM ( @dbName))
    
        SET @index = CHARINDEX ( ',' , @tabList)
    
        IF LEN ( @dbName) > 0
    
           SET @tabPrefix = @dbName + '..'
    
        ELSE
    
           SET @tabPrefix = '' ;
    
        WHILE @index > 0 AND @index IS NOT NULL
    
        BEGIN
    
           SET @tabName = SUBSTRING ( @tabList, 1, @index- 1)       
    
           SET @wi= CHARINDEX ( ' where' , LTRIM ( @tabName))
    
           IF @wi= 0
    
               SET @wi = LEN ( @tabName)
    
     
           INSERT INTO @t_tb( tb, Sqlwhere) VALUES ( SUBSTRING ( @tabName, 1, @wi), SUBSTRING ( @tabName, @wi+ 1, LEN ( @tabName)- @wi))
    
           SET @tabList = SUBSTRING ( @tabList, @index+ 1, LEN ( @tabList)- @index)
    
           SET @index = CHARINDEX ( ',' , @tabList)
    
        END
    
        IF @index = 0 OR @index IS NULL
    
           SET @tabName = @tabList
    
        ELSE
    
           SET @tabName = SUBSTRING ( @tabList, 1, @index)
    
        SET @wi= CHARINDEX ( ' where' , LTRIM ( @tabName))
    
    
        IF @wi= 0
    
           SET @wi = LEN ( @tabName)
    
        INSERT INTO @t_tb( tb, Sqlwhere) VALUES ( SUBSTRING ( @tabName, 1, @wi), SUBSTRING ( @tabName, @wi+ 1, LEN ( @tabName)- @wi))
    
        SELECT @SQL1 = 'select INSERT_SQL='';SET NOCOUNT ON'  +  ' union all '
    
        DECLARE tab_cur CURSOR FOR
    
        SELECT t. name , tb. Sqlwhere FROM sys.tables t
    
        INNER JOIN @t_tb tb ON t. name = RTRIM ( LTRIM ( tb. TB))
    
        ORDER BY tb. SN     
    
        OPEN tab_cur
    
        FETCH NEXT FROM tab_cur INTO @tabName, @SQLWhere
    
        WHILE @@FETCH_STATUS = 0 BEGIN
    
           DELETE FROM @colList
    
           IF NOT EXISTS( SELECT 1 FROM sys.objects WHERE name = @tabName AND type = 'U' ) BEGIN
    
               PRINT ( @tabName + N' no exist! ' )
    
               RAISERROR ( @tabName, 16, - 1);
    
               FETCH NEXT FROM tab_cur INTO @tabName, @SQLWhere
    
               CONTINUE ;
    
           END
    
          
    
           INSERT INTO @colList( colName, colType, colValueL, colValueR)
    
           SELECT c. NAME , t. name , '' , ''
    
           FROM sys.columns c
    
           INNER JOIN sys.tables tab
    
               ON c. object_id = tab. object_id
    
           INNER JOIN sys.types t
    
               ON c. user_type_id = t. user_type_id
    
           WHERE c. is_computed= 0
    
               AND tab. name = @tabName
    
     
    
           IF @IncludeIdentity= 0
    
               DELETE FROM @colList WHERE colName IN(
    
                  SELECT name FROM sys.columns WHERE object_id = OBJECT_ID ( @tabName) AND is_identity= 1)
    
                 
    
           UPDATE @colList SET colValueL= 'RTRIM(' , colValueR = ')'
    
           WHERE colType IN( 'text' , 'varchar' , 'nvarchar' , 'char' , 'uniqueidentifier' , 'datetime2' , 'nchar' , 'sysname' )
    
          
           SELECT @cols= '' , @colsData = '' , @SQL = '' ;
    
           UPDATE @colList SET colName = '[' + colName + ']'    
    
           UPDATE @colList SET selColName= colName   
    
          
           UPDATE @colList SET colValueL= 'replace(' + colValueL, colValueR = colValueR+ ','''''''','''''''''''')'
    
           WHERE colType IN( 'text' , 'varchar' , 'nvarchar' , 'char' , 'nchar' , 'sysname' )  
    
              
    
           UPDATE @colList SET colValueL=
    
               CASE WHEN colType IN( 'text' , 'varchar' , 'nvarchar' , 'char' , 'uniqueidentifier' , 'datetime2' , 'nchar' , 'sysname' ) THEN '''''''''+' ELSE '' END
    
                  + colValueL,
    
               colValueR = colValueR + CASE WHEN colType IN( 'text' , 'varchar' , 'nvarchar' , 'char' , 'nchar' , 'datetime2' , 'uniqueidentifier' , 'sysname' ) THEN '+''''''''' ELSE '' END
    
           SELECT @cols = @cols + colName + ', ' ,
    
               @colsData = @colsData + 'isnull(' +
    
                  colValueL +          
    
                  CASE WHEN colType= 'datetime2' THEN 'convert(varchar(20),' + colName+ ',120)'
    
                  WHEN colType= 'uniqueidentifier' THEN 'convert(varchar(50),' + colName+ ')'
    
                  WHEN colType= 'text' THEN 'convert(nvarchar(max),' + colName+ ')'
    
                  WHEN colType= 'sysname' THEN 'convert(nvarchar(max),' + colName+ ')'
    
                  WHEN colType= 'varbinary' OR colType= 'BINARY' OR colType= 'image'
    
                      THEN 'master.dbo.fn_varbintohexsubstring(1,' + colName+ ',1,0)'              
    
                  ELSE   'cast(' + colName+ ' as nvarchar(max))' END
    
                  + colValueR + ',''null'')+'', ''+'
    
           FROM @colList
    
           SELECT @cols = LEFT( @cols, LEN ( @cols)- 1),
    
                  @colsData = LEFT( @colsData, LEN ( @colsData)- 5),
    
                  @SQL =  'select INSERT_SQL='''' union all '
    
           SELECT @cols = 'select INSERT_SQL=''INSERT INTO ' + @tabPrefix + @tabName + '(' + @cols+ ')' ,
    
               @colsData = ' VALUES(''+' + @colsData + '+'');'' FROM ' + @tabPrefix + @tabName
    
           SELECT @colsData = @colsData + ' ' + ISNULL ( @SQLWhere, '' ) 
    
           IF @IncludeIdentity= 1 AND EXISTS( SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID ( @tabName) AND is_identity= 1)
    
           BEGIN
    
               SELECT @SQLIdentityOn = 'select INSERT_SQL=''SET IDENTITY_INSERT ' + @tabPrefix + @tabName + ' ON;''' +
    
                      ' union all ' ,
    
                  @SQLIdentityOff = ' union all ' + 'select INSERT_SQL=''SET IDENTITY_INSERT ' + @tabPrefix + @tabName + ' OFF;'''
    
           END
    
           ELSE
    
           BEGIN
    
               SELECT @SQLIdentityOff = '' ,
    
                  @SQLIdentityOn = '' ;
    
           END
    
           INSERT INTO @tb( insert_sql)
    
           EXECUTE (  @SQL+ @SQLIdentityOn + @cols+ @colsData + @SQLIdentityOff)
    
           FETCH NEXT FROM tab_cur INTO @tabName, @SQLWhere
    
        END
    
        CLOSE tab_cur
    
        DEALLOCATE tab_cur
    
        SELECT insert_sql FROM @tb
    
    END
    
    GO
    

    用法: tabList以,分隔。

     @GetMapping( "/getInsert")
     public ReturnResult getInsertSql(String tabList) {
            ReturnResult retVal = new ReturnResult();
            try {
                 List<?> resultList = jdbcTemplate.execute(new CallableStatementCreator() {
                    public CallableStatement createCallableStatement(Connection con) throws SQLException {
                        String storedProc = "{call sp_get_InsertSql(?)}";
                        CallableStatement cs = con.prepareCall(storedProc);
                        cs.setString(1, tabList);
                        return cs;
                    }
                }, cs-> {
                    List<String> list = new ArrayList<>();
                    try (ResultSet rs = cs.executeQuery()) {
                        ResultSetMetaData rmd = rs.getMetaData();
                        int columnCount = rmd.getColumnCount();
                        while (rs.next()) {
                            list.add("" + rs.getObject(columnCount));
                        }
                    } 
                    return list;
                });
                retVal.setResult(resultList);
                return retVal;
            } catch (Exception e) {
                retVal.setMessage(e.getMessage());
                retVal.setFlag(false);
                retVal.setErrorCode(ViBorderUtil.getErrorCode(ErrorCode.E250001));
                return retVal;
            }
    }
    

    相关文章

      网友评论

          本文标题:MSSQL存储过程生成insert into 语句

          本文链接:https://www.haomeiwen.com/subject/bplbtktx.html