將資料庫中的資料轉換為insert語句

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（視欄位多少及欄位名稱長度而定）