SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

特定ディレクトリ内のファイルを PowerShell で SQL Server に一括で格納

leave a comment

SQL Server 2012 では PowerShell 強化が挙げられていたと思うのですが、今までほとんど使ったことがありませんでした。
SQL Server PowerShell

フルテキストインデックスのテスト用のファイルを varbinary(max) に大量に格納したかったので、PowerShell で作ってみました。

■PowerShell で varbinary にデータを格納


データを格納するために使用するテーブルは以下の構造となっています。
image

CREATE TABLE [dbo].[tbl_FullTextTest](
    [ID] [uniqueidentifier] NOT NULL,
    [FileName] [nvarchar](260) NULL,
    [Extension] [nvarchar](20) NULL,
    [OriginalFileName] [nvarchar](max) NULL,
    [FileBinary] [varbinary](max) NULL,
    [InsertDate] [datetime] NULL,
    [FtTimestamp] [timestamp]
CONSTRAINT [PK_tbl_FullTextTest] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)) ON [PRIMARY]

 

フルテキストインデックスの増分更新をするために timestamp の列を作成しています。

このテーブルに PowreShell でファイルを格納していきます。
スクリプトがこちらになります。

# Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
Import-Module "sqlps"  -DisableNameChecking
# Set-Location SQLSERVER:SQLML110G6-01SQL2012DatabasesTEST

$ServerInstance=".SQL2012"
$Database="TEST"
$InsertFilePath="C:tempEventTech Ed 2012 Europe"

$files = Get-ChildItem $InsertFilePath -Recurse | Where-Object {$_.mode -notmatch "d"}

foreach ($file in $files){
    $val = "FileName=$($file.Name)", "FilePath=$($file.FullName)", "Extension=$($file.extension)"
    $sql = "INSERT INTO tbl_FullTextTest(ID, FileName, Extension, OriginalFileName,InsertDate, FileBinary)
            SELECT NEWID() , N’`$(FileName)’, N’`$(Extension)’, N’`$(FilePath)’, GETDATE(), *
            FROM OPENROWSET(BULK N’`$(FilePath)’, SINGLE_BLOB) AS document"
    $file.FullName
    Invoke-Sqlcmd $sql -ServerInstance $ServerInstance -Database $Database -Variable $val -SuppressProviderContextWarning
}

 

特定のディレクトリ配下のファイルを OPENROWSET を使用してテーブルに格納していきます。
フルテキストインデックスのテスト用にファイルを挿入した日やファイルのパスなども格納しながらテーブルにデータを挿入していきます。

Invoke-Sqlcmd をパラメータクエリで実行するために配列として変数を格納しているのですが、文字列の中に変数のプロパティを展開する方法がわからず、twitter で呟いてみたところ教えていただけました。ありがとうございます!!

フルテキストインデックスのテストをする際にはそれなりなファイル量が必要になってくると思いますので一括で入れられる方法があると便利かと。

Written by masayuki.ozawa

9月 27th, 2012 at 10:43 pm

Posted in PowerShell,SQL Server

Tagged with ,

Leave a Reply

*