sqlserver - insert - 一括取込 - bulk,bcp,他1種

それぞれの利点

bcp : inとout両方とも書ける
bulk : わかり易い 取込時に置換とかの条件を付けられる

書き方

bcp_出力 (windows認証)

bcp_out.sql

  -- 下３つ必要ない場合もあるけど大体必要だと思う
  EXECUTE sp_configure 'show advanced options', 1;  
  RECONFIGURE;  
  EXECUTE sp_configure 'Ole Automation Procedures', 1;
  RECONFIGURE;
  EXECUTE sp_configure 'xp_cmdshell', 1;
  RECONFIGURE;

  declare @bcpcmd varchar(1000)
  set @bcpcmd =           'BCP DB.dbo.tablename '
  set @bcpcmd = @bcpcmd + 'OUT "C:\Users\oonaa\Desktop\is_output.CSV" '
  set @bcpcmd = @bcpcmd + '-c '
  set @bcpcmd = @bcpcmd + '-S localdb '
  set @bcpcmd = @bcpcmd + '-T '
  exec master..xp_cmdshell @bcpcmd

bcp_取込 (windows認証)

bcp_in.sql

  -- 下３つ必要ない場合もあるけど大体必要だと思う
  EXECUTE sp_configure 'show advanced options', 1;  
  RECONFIGURE;  
  EXECUTE sp_configure 'Ole Automation Procedures', 1;
  RECONFIGURE;
  EXECUTE sp_configure 'xp_cmdshell', 1;
  RECONFIGURE;

  declare @bcpcmd varchar(1000)
  set @bcpcmd =           'BCP DB.dbo.tablename '
  set @bcpcmd = @bcpcmd + 'IN "C:\Users\oonaa\Desktop\is_output.CSV" '
  set @bcpcmd = @bcpcmd + '-c '
  set @bcpcmd = @bcpcmd + '-S localdb '
  set @bcpcmd = @bcpcmd + '-T '
  exec master..xp_cmdshell @bcpcmd

bulk_取込

bulk_insert.sql

begin try
  bulk insert
    tablename
  from
    'C:\Users\oonaa\Desktop\in_torikomi.csv'
  with(
    FIELDTERMINATOR = ',',  -- カンマ区切り:,、タブ区切り：\t
    ROWTERMINATOR = '\n' ,  -- 一行の終わりを示す文字
    KEEPNULLS,
    ERRORFILE = 'C:\Users\oonaa\Desktop\ERR.csv' -- エラー出力ファイル
    --,FIRSTROW = 2,  -- 2行目から読み込み (タイトル行を読まない)
  )
end try
begin catch
  select
    ERROR_NUMBER()    AS ErrorNumber,
    ERROR_SEVERITY()  AS ErrorSeverity,
    ERROR_STATE()     AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE()      AS ErrorLine,
    ERROR_MESSAGE()   AS ErrorMessage
end catch

catch内でエラー取得

後は動作確認できなかったbcp_出力 (sql-server認証)

bcp_out.sql

  -- 下３つ必要ない場合もあるけど大体必要だと思う
  EXECUTE sp_configure 'show advanced options', 1;  
  RECONFIGURE;  
  EXECUTE sp_configure 'Ole Automation Procedures', 1;
  RECONFIGURE;
  EXECUTE sp_configure 'xp_cmdshell', 1;
  RECONFIGURE;

  declare @bcpcmd varchar(1000)
  set @bcpcmd =           'BCP DB.dbo.tablename '
  set @bcpcmd = @bcpcmd + 'IN "C:\Users\oonaa\Desktop\is_output.CSV" '
  set @bcpcmd = @bcpcmd + '-c '
  set @bcpcmd = @bcpcmd + '-S 192.168.11.2 '
  set @bcpcmd = @bcpcmd + '-U admin '
  set @bcpcmd = @bcpcmd + '-P admin '
  exec master..xp_cmdshell @bcpcmd

欠点

bcp　:t-sql上ではEXECUTE master..xp_cmdshell @bcpcmd みたいに動的変数を経由しないと使えないのが困る
　　　cmd上で動くのが気になる
　　　bulkより若干遅い（らしい）
bulk : insertのみしかできずoutができない