CREATE PROCEDURE sp_generate_insert_script @table_list varchar(8000)='*'
--Format of @table_list:
--'tb'        --single table
--'t1,t2,t3'  --table list
--'*'         --all tables in the database
AS
--作者：pbsql
DECLARE @table_name  nvarchar(128)
DECLARE @column_list varchar(8000)
DECLARE @values_list varchar(8000)
DECLARE @sql         varchar(8000)
DECLARE @msg         varchar(8000)

CREATE TABLE #result(sql varchar(8000))

IF @table_list='*'
BEGIN
  SELECT @table_list=@table_list+','+name
    FROM sysobjects
    WHERE xtype='U' AND name<>N'dtproperties'
  SET @table_list=STUFF(@table_list,1,2,'')
END

WHILE @table_list<>''
BEGIN
  IF CHARINDEX(',',@table_list)>0
    SELECT @table_name=LEFT(@table_list,CHARINDEX(',',@table_list)-1),
           @table_list=STUFF(@table_list,1,CHARINDEX(',',@table_list),'')
  ELSE
    SELECT @table_name=@table_list,
           @table_list=''

  IF EXISTS(SELECT 1 FROM sysobjects WHERE xtype='U' AND name=@table_name)
  BEGIN
    SELECT @column_list='',@values_list=''
    SELECT
      @column_list=@column_list+','+name,
      @values_list=@values_list+'+'',''+'+
        CASE WHEN xtype IN(175,167,36) THEN--char,varchar,uniqueidentifier
          'ISNULL(''''''''+REPLACE('+name+','''''''','''''''''''')+'''''''',''NULL'')'
             WHEN xtype in(239,231) THEN--nchar,nvarchar
          'ISNULL(''N''''''+REPLACE('+name+','''''''','''''''''''')+'''''''',''NULL'')'
             WHEN xtype in(61,58) THEN--datetime,smalldatetime
          'ISNULL(''''''''+CONVERT(char(23),'+name+',121)+'''''''',''NULL'')'
             ELSE--digital
          'ISNULL(CONVERT(varchar(20),'+name+'),''NULL'')'
        END
      FROM (SELECT a.name,a.xtype
              FROM syscolumns a,sysobjects b
              WHERE b.xtype='U' AND b.name=@table_name AND a.id=b.id
                AND a.xtype NOT IN(173,165,34,35,99,98,189)
              --NOT binary,varbinary,image,text,ntext,sql_variant,timestamp
           )t

    SELECT @column_list=STUFF(@column_list,1,1,''),
           @values_list=STUFF(@values_list,1,4,''),
           @sql='SELECT ''INSERT INTO '+@table_name+'('+@column_list+')'
               +' VALUES('''+@values_list+'+'')'' sql FROM ['+@table_name+']'

    IF OBJECTPROPERTY(OBJECT_ID(@table_name),'TableHasIdentity')=1
      INSERT INTO #result(sql)
      EXEC('SELECT ''--Table Name: '+@table_name+''' sql UNION ALL '
          +'SELECT ''SET IDENTITY_INSERT '+@table_name+' ON '' sql UNION ALL '
          +@sql+' UNION ALL '
          +'SELECT ''SET IDENTITY_INSERT '+@table_name+ ' OFF '' sql')
    ELSE
      INSERT INTO #result(sql)
      EXEC('SELECT ''--Table Name: '+@table_name+''' sql UNION ALL '+@sql)
  END
  ELSE
  BEGIN
    SET @msg='Can''t generate the insert script of the table '''+@table_name
            +''', because it does not exist in the system catalog.'
    DROP TABLE #result
    RAISERROR(@msg,16,1)
    RETURN
  END
END
SELECT sql FROM #result
DROP TABLE #result
GO

调用格式举例：
sp_generate_insert_script 'tb'--转换表tb中的数据
sp_generate_insert_script 't1,t2,t3'--转换表t1,t2,t3中的数据
sp_generate_insert_script '*'--转换所有表中的数据

优点：
1.支持三种参数格式，调用方便
2.支持标识列的导出
3.将结果放在一个结果集中，方便拷贝

缺点：
1.不支持以下类型：binary,varbinary,image,text,ntext,sql_variant,timestamp
2.由于受SQL SERVER字符串长度影响（8000B），若表中字段过多或表中数据过长会被截断而出错，但一般的需求可以满足，最大可以支持每表100个字段左右，每条记录最大支持为6-7KB（视字段多少及字段名称长度而定）

发表于 @ 2006年02月21日　20:18:00 | 评论( loading... ) | 编辑| 举报| 收藏

旧一篇:将指定字符串拆分为两个字段的记录集