TRYER.net-网络技术,黑客,WEB编程,热门网站,热门软件,学院

Create PROC [dbo]. [sp_get_InsertSql] 

    @dbName              VARCHAR ( 32)= '' ,    -- 数据库名称 

    @tabList          VARCHAR ( max ), -- 要导出数据的表名，表名之间用逗号隔开，过滤条件跟在表名后面，用空格隔开如 tab1 where col1!=2, tab2, tab3   

    @IncludeIdentity  BIT = 1,         -- 是否包含自增字段 

    @DeleteOldData       BIT = 1         -- 插入前删除所有数据 

AS 

    DECLARE 

       @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 ), 

       @SQLDelete    VARCHAR ( max ), 

       @SQLIfBegin       VARCHAR ( 1024), 

       @SQLIfEnd     VARCHAR ( 1024), 

       @SQLNull      VARCHAR ( 1024);        

    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' + CHAR ( 13) + '''' + 

                  ' union all ' 

    SELECT @SQLNull = 'select INSERT_SQL=''  '' union all ' ,      

          @SQLIfBegin = 'select INSERT_SQL=''    If @Error=0 begin ''' + 

                  ' union all ' , 

          @SQLIfEnd = ' union all ' + 'select INSERT_SQL=''    end;''' 

    

    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' 不存在！ ' ) 

           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' , 'datetime' , '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' , 'datetime' , 'nchar' , 'sysname' ) THEN '''''''''+' ELSE '' END 

              + colValueL, 

           colValueR = colValueR + CASE WHEN colType IN( 'text' , 'varchar' , 'nvarchar' , 'char' , 'nchar' , 'datetime' , 'uniqueidentifier' , 'sysname' ) THEN '+''''''''' ELSE '' END 

              

       SELECT @cols = @cols + colName + ', ' , 

           @colsData = @colsData + 'isnull(' + 

              colValueL +           

              CASE WHEN colType= 'datetime' 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=''print ''''Table Name:  ' + CHAR ( 9)+ @tabName + '''''''' + 

                  ' union all ' 

       
--这里可以加入top参数指定取出多少记录
       SELECT @cols = 'select INSERT_SQL=''INSERT INTO ' + @tabPrefix + @tabName + '(' + @cols+ ')' , 

           @colsData = '  VALUES(''+' + @colsData + '+'');'' FROM ' + @tabPrefix + @tabName 

       SELECT @colsData = @colsData + ' ' + ISNULL ( @SQLWhere, '' ) 

       

       IF @DeleteOldData= 1  

           SET @SQLDelete = 'select INSERT_SQL='''' + 

                  ''Delete from ' + @tabPrefix + @tabName + '; ''' + 

                  ' union all ' 

       ELSE 

           SET @SQLDelete= '' 

       

       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 ( @SQLNull + @SQLIfBegin + @SQL+ @SQLDelete+ @SQLIdentityOn + @cols+ @colsData + @SQLIdentityOff + @SQLIfEnd) 

  

       FETCH NEXT FROM tab_cur INTO @tabName, @SQLWhere 

    END 

  

    CLOSE tab_cur 

    DEALLOCATE tab_cur 

       

    SELECT insert_sql FROM @tb ORDER BY sn 

END 

EXEC sp_get_InsertSql @dbName='test', @tabList='ProvinceAndCity', @IncludeIdentity=1, @DeleteOldData=0