Powershell--批量拆分SQL語句為事務並批處理

作為DBA，時不時會遇到將數據導入到資料庫的情況，假設業務或研發提供一個包含上百萬行INSERT語句的腳本文件，而且這些INSERT 語句沒有使用GO來進行批處理拆分，那麼直接使用SQLCMD來執行會發現該進程消耗大量物理記憶體並遲遲沒有數據寫入，即使腳本中每一行都添加了GO，你依然會發現這插入效率太差，讓你無法忍受，怎麼搞呢，下麵小代碼幫你折騰下:

$old_file_path= 'C:\insert_scripts.sql'
$new_file_path='C:\insert_scripts_new.sql'
$tran_rows=50

$line_content="
SET NOCOUNT ON
GO
BEGIN TRAN
BEGIN TRY
"
$line_content |Out-File $new_file_path
$line_num=1

$sr1=[IO.StreamReader]$old_file_path
$line_content=""
while(-not $sr1.EndOfStream)
{
    
    $tmp_content= $sr1.ReadLine()
    $line_content=$line_content+"`r"+$tmp_content
    $line_num_mode =$line_num % $tran_rows
    
    if($line_num_mode -eq 0)
    {
        $line_content=$line_content+"
COMMIT
PRINT '執行第"+($line_num-$tran_rows)+"行到第"+($line_num)+"行成功'
END TRY
BEGIN CATCH
ROLLBACK
PRINT '執行第"+($line_num-$tran_rows)+"行到第"+($line_num)+"行失敗'
END CATCH
GO
BEGIN TRAN
BEGIN TRY
"
        $line_content | Out-File -Append $new_file_path
        Write-Host "處理到行" $line_num
        $line_content=""
    }

    $line_num=$line_num+1
    
}

$line_content=$line_content+"
COMMIT
PRINT '執行第"+($line_num-$tran_rows)+"行到第"+($line_num)+"行完成'
END TRY
BEGIN CATCH
ROLLBACK
PRINT '執行第"+($line_num-$tran_rows)+"行到第"+($line_num)+"行失敗'
END CATCH
GO
"
$line_content | Out-File -Append $new_file_path

$sr1.Close()

還是看點療效吧，原始SQL為：

經過此腳本修改過的變為：

這樣實現有以下有點：

1.　　使用“SET NOCOUNT ON”來屏蔽每條語句返回影響行數，減少與cmd視窗交互的次數;

2.　　將每50條語句拆分到一個批處理中，降低資料庫進行語法檢查分析的消耗，在封裝到一個事務中進行提交，減少寫日誌的次數;

3.　　列印輸出事務執行結果，方便排查錯誤（可修改代碼只輸出執行失敗的事務）

執行結果截圖：

====================================================

在個人電腦測試，以100條一個事務來拆分，大概1分鐘可以導入50萬到60萬，按不同的行數進行拆分插入效率不同，具體合適的拆分行數需要根據實際情況測試為準。

對於超大數據量的導入，還是推薦使用csv+bcp的方式來導入，INSERT+SQLCMD的效率始終太低太低！

====================================================

沒啥技術含量，厚臉拿出來分享，只是因為很久沒寫博客啦！