powershellでcsvデータをSQL挿入/更新。insertとupdateを一度の読み込みで。
SQLを組む機会が巡ってきた。 MS SQL Server EXPRESSではSQL Server Agentが使用できないため SQL文を作成し、定期実行というのができないので powershellでSQLを実行できるようにしてタスクスケジューラに配置する必要がある。
■install-module sqlserver
powershell のsqlserver用モジュールをインストールする
■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の仕組みを確認したことで進んだ気がする。