Ryo's Log

26才(現在31才)が一回人生を仕切り直して始めていくブログです。主に技術ログ。

powershellでcsvデータをSQL挿入/更新。insertとupdateを一度の読み込みで。

SQLを組む機会が巡ってきた。 MS SQL Server EXPRESSではSQL Server Agentが使用できないため SQL文を作成し、定期実行というのができないので powershellSQLを実行できるようにしてタスクスケジューラに配置する必要がある。

■install-module sqlserver

powershellsqlserver用モジュールをインストールする

■write-sqltabledataとinvoke-sqlcmd

データ変更には2種類のコマンドがあるがinvoke-sqlcmdを選択 write-sqltabledataには認証が-credentialでの接続しかできず、うまく接続ができなかったため invoke-sqlcmdではuserとpasswordが明確に設定できた。

sqlのクエリ

csvファイルを取り込む際はbulk insertが使われるが 今回の仕様として何度も取り込むので、このままでは主キーがかぶってしまい更新が行われない。

そのためmergeとusing openrowset ( bulkを利用することにした。 これを利用する場合はformatファイルが必要になるため別のコマンドで作成する

openrowsetはファイルからリモートテーブルを準備できるコマンド 単品でデータが読み込めているかどうか確認をしてから 組み込んだ。

Invoke-Sqlcmd -query "
(select  * from  openrowset ( bulk 'c:\temp\add.csv',
                    formatfile = 'c:\temp\test.xml',
                    Firstrow = 2) as new) " `  -ServerInstance '.\SQLEXPRESS'
■コマンド内容(列名は適当に伏せています)
Invoke-Sqlcmd -query "
merge into [dbo].[test]
using   openrowset ( bulk 'c:\temp\add.csv',
                    formatfile = 'c:\temp\test.xml',
                    firstrow = 2) as new
ON [test].[a] = new.[a]
WHEN MATCHED THEN
    update set
        b = new.a,
        c = new.b,
        d = new.c,
        e = new.d,
        f = new.e
WHEN NOT MATCHED THEN
    insert (
        a,
        b,
        c,
        d,
        e,
        f
        )
    values (
    new.a,
    new.b,
    new.c,
    new.d,
    new.e,
    new.f
    );" ` -ServerInstance '.\SQLEXPRESS'
■フォーマットファイル作成
bcp [dbo].[test] format null  -S '.\SQLEXPRESS' -f c:\temp\test.xml -x -c -T -t ','
■フォーマット内容
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="50" COLLATION="Japanese_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME='a' xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="b" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="c" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="d" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="e" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="6" NAME="f" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>
csvファイルの文字コード

csvはshift-jisで保存

■所感

1日かかってしまった。openrowsetの仕組みを確認したことで進んだ気がする。