sql server 2005导出Insert语句

创建存储过程：

-- =======================================================================================


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 '


      


       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='GSR',  
  @tabList='GSR_M_MOD WHERE MMDMUSER =''Jonny.Wang''',
    @IncludeIdentity=1,
    @DeleteOldData=0