SQL Server PowerShell を使ってBulk Insertを実行する

スポンサーリンク
 ラベル: ,
SQL Serverに大量データをインサートする場合、Bulk Insertをよく利用しています。
利用するたびにSQL Server Management Studioを起動していますが、環境によっては起動が遅いので困っています。
より早く、簡単、そして手軽に実行するためSQL Server PowerShell を使ってBulk Insertを実行するサンプルを書いてみました。
2015.04.19 加筆
New-PSDrive コマンドレットに-Credential パラメーターを追記しました。

やりたい事

  • Windows クライアントから、大量のCSVファイルをSQL Serverにインポートしたい
  • インポートはBulk Insertを使いたい

事前環境

  • Windows クライアントでSQL Server PowerShellが実行できる事
  • 共有フォルダーへ接続できる事

フロー

  1. 共有フォルダーに接続
  2. 共有フォルダーにCSVファイルをコピー
  3. 全CSVファイルパスを取得
  4. Bulk Insertを実行
  5. インポートが完了後、ファイルの拡張子をリネーム
  6. 共有フォルダーを切断

サンプル

Windows クライアントからBulk Insertを実行するため、Invoke-Sqlcmd コマンドレットを使います。
$ServerName = '<サーバー名>'
$FolderPath = "\\$ServerName\<共有フォルダー名>"
$PsDrive = "<ドライブ名>"
$Table = "[<データベース名>].[<スキーム名>].[<テーブル名>]"
$RenameExtention = "<拡張子名>"
$Cnt = 1

Write-Host "Bulk Insertを開始します" -ForegroundColor Cyan

#共有フォルダーに接続
try
{
    if((Test-Path "$PsDrive`:") -eq $false)
    {
        #New-PSDrive コマンドレットは、環境によって-Credential パラメーターを付けてください。
        $tmp = New-PSDrive -Name $PsDrive -PSProvider FileSystem -Root $FolderPath
        Write-Debug "共有フォルダーに接続しました"

        #Copy Files
        Copy-Item -Path "<ローカル パス>\<ファイル>" -Destination $PsDrive
    }
}
catch [Exception]
{
    Write-Host "エラー：パスが存在しません。" -BackgroundColor Yellow
    exit
}

#Get Files
$files = gci "$PsDrive`:" -Exclude "*.$RenameExtention" -Recurse -ErrorAction Stop
Write-Debug "対象ファイルを取得しました"

foreach($f in $files)
{
    #Bulk Insert SQL
    $s = @"
BULK INSERT $Table 
FROM "$f" WITH (
FIELDTERMINATOR = '\t'
, ROWTERMINATOR = '\n'
, FIRSTROW = 2)
"@
    try
    {
        #Exec SQL
        Invoke-Sqlcmd -Query $s -ServerInstance $ServerName
        Write-Host "  `#$Cnt Import Finished!!" 
    
        #Rename Extention
        Rename-Item -Path $f -NewName ($f -replace '.txt$', ".$RenameExtention")
        Write-Debug "  拡張子をリネームしました"

        $Cnt += 1
    }
    catch [Exception]
    {
        Write-Host "エラーが発生しました" -ForegroundColor Yellow
        Write-Host $Error -ForegroundColor Yellow

        exit
    }
}

#Remove PSDrive
if((Test-Path $FolderPath) -eq $true)
{
    Remove-PSDrive -Name $PsDrive -Force
    Write-Debug "共有フォルダーを切断しました"
}

Write-Host "完了しました" -ForegroundColor Cyan
関連するリンク
スポンサーリンク

スポンサーリンク