sql server 2005生成insert语句，同时完成多表

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

/*

Example:  

EXECsp_get_InsertSql @dbName='',

    @tabList='tb1 where id=0,

            tb2 WHERE id=0,

            tb3, tb4',

    @IncludeIdentity=1,

    @DeleteOldData=0

   

*/

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

ALTER 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 NOCOUNTON

    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 = 'selectINSERT_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 BYtb.SN   

   

    OPEN tab_cur

    FETCH NEXT FROM tab_cur INTO@tabName, @SQLWhere

    WHILE @@FETCH_STATUS=0 BEGIN

       DELETE FROM @colList

             

      

       IF NOTEXISTS(SELECT 1FROM sys.objectsWHERE 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.columnsc

       INNER JOINsys.tables tab

           ON c.object_id = tab.object_id

       INNER JOINsys.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 WHEREcolName 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=''INSERTINTO ' + @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=''SETIDENTITY_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