バッチファイルからマスタデータを取得する方法

Question

バッチファイルからSQLServerにあるDBの指定したテーブルのデータを取得し
取得したデータを違うサーバへインサートする処理を作成しています。
SQL文を別ファイルで作成し、SQLコマンドで呼び出すように進めています。

以下がコードです。

rem ## フォルダ設定
rem ルートDIR
set root_dir=’ルートDIR’
rem ログファイルフルパス
set log_name=%root_dir%フルパス.log
rem データ保管先
set get_dir=D:'保管先'

rem     データ取得フォルダ作成
if not exist %get_dir% mkdir %get_dir%
if not exist %get_dir%\%year% mkdir %get_dir%\%year%
if not exist %get_dir%\%year%\%month% mkdir %get_dir%\%year%\%month%

rem     データ取得
SQLCMD -S サーバ名 -U ユーザ名 -P パスワード -i %root_dir%DATA.SQL -s, -W -h -1 -o Qutput.csv >> %log_name%
SQLCMD -S サーバ名 -U ユーザ名 -P パスワード -i %root_dir%INSERT.SQL >>  %log_name%

＊DATA.SQL＊
 
DECLARE @d datetime = GETDATE()
DECLARE @d1 char(8) = CONVERT(char(8), @d, 112)
DECLARE @d2 char(6) = REPLACE( CONVERT(char(8), @d, 108), ':', '' )
DECLARE @d3 char(4) = CONVERT(char(4), @d, 111)
DECLARE @d4 char(2) = CONVERT(char(2), @d, 1)

DECLARE @eName varchar(70) = 'D:\Dataget\SQLServer\data\' + @d3 + '\' + @d4 + '\' + @d1 + @d2 + _dbMst' +'.csv'

SELECT * FROM  sys.objects  where type ='U' AND name NOT IN('CUSTOM','PRO')= @eName

＊INSERT.SQL ＊

DECLARE @d datetime = GETDATE()
DECLARE @d1 char(8) = CONVERT(char(8), @d, 112)
DECLARE @d2 char(6) = REPLACE( CONVERT(char(8), @d, 108), ':', '' )
DECLARE @d3 char(4) = CONVERT(char(4), @d, 111)
DECLARE @d4 char(2) = CONVERT(char(2), @d, 1)

DECLARE @eName varchar(70) = ¥’保存先’¥ + @d3 + '\' + @d4 + '\' + @d1 + @d2 + '_dbMst_INSERT' + '.csv'

BULK INSERT  'テーブル名'  FROM  DISK =@eName
WITH
(
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n'
)

実行してみるても、ファイルは作成されるのですがlog内が空白なのと、
データの保管先であるget_dirにcsvファイルが作成されません。
なにかアドバイスをいただけないでしょうか。

Comments

[sazi]

単なる環境構築とか、連携処理の類とかの背景を追記された方が良いかと思います。

Answer 1

bcpコマンドですが、簡単に書くと下記のようになります。

エクスポート

bcp テーブル名 out "出力ファイル名" -S サーバー名 -U ユーザー名 -P パスワード

インポート

bcp テーブル名 in "入力ファイル名" -S サーバー名 -U ユーザー名 -P パスワード

詳しい使い方に関しては検索してみてください。

Comments

[maam]

ご回答ありがとうございます。調べてやってみます！

Answer 2

SQLServerにあるDBの指定したテーブルのデータを取得し、取得したデータを違うサーバへインサートする処理を作成しています。

突っ込み処が満載ですね。

・sys.objectsから取得できるのはテーブル名であってテーブルの内容ではない。
・テーブルが違えば、レイアウトが異なるので同じCSVで処理はできない。

取り敢えずはこれ位。

データの移植なら、バックアップ/リストアで良さそうですが？

Comments

[maam]

ご回答ありがとうございます。
＞・sys.objectsから取得できるのはテーブル名であってテーブルの内容ではない。
SQLServerでこのSQL文を実行するとデータが表示されたのですが、これでは内容が取得できてないのですか？

＞・テーブルが違えば、レイアウトが異なるので同じCSVで処理はできない。
このDBにはテーブルが70件あるのですが、70件分のcsvを作成する必要があるということですか？

[sazi]

sys.objectsはSQLServerのシステムカタログです。
管理しているテーブルなどのオブジェクト一覧で中身ではありません。

[sazi]

ＣＳＶってヘッダーにある名前や項目数は同じですよね。
７０個のテーブルのレイアウトは同じなんですか？
仮に同じでも、それを一つのファイルに纏めたら、どう振り分けるつもりですか？

[maam]

70個のテーブルのレイアウトはバラバラです。
70個分のCSVファイルを作成しないといけませんね…

[sazi]

バックアップ/リストアでは不都合があるのでしょうか？

[maam]

その考えがありませんでした＞＜
バックアップ/リストアで方法を調べてみます。

[YAmaGNZ]

横からですが、bcpという手もありかと思います。

[maam]

それはバルクインサートと同じような処理のことですか？？

[sazi]

> YAmaGNZ さん
70個のテーブルという事で、バックアップ/リストア時にテーブルを指定できますし、ファイルは一つの方が良いのでは無いかと思った次第

[YAmaGNZ]

>saziさん
質問の元がバッチファイルでの実行だったので、日次処理的な感じでスケジューラなどでの実行も視野にあるのかと思い、バッチで簡単に使えるbcpも選択肢として考えたほうがいいのかと提案してみました。

Answer 3

2点気になります。的外れだったらゴメンナサイ。

• SQL実行コマンドの引数にある「%root_dir%DATA.SQL」ですが、スラッシュかバックスラッシュで区切らないと不正なパスになりませんか？（上段のif文にあるようにスラッシュ区切りにしないとダメでは？）
• 冒頭でroot_dirを定義していますが、パス文字列をシングルクォーテーションで囲ってしまうと、パス文字列にシングルクォートが入るように思います。下記のようにしてみてどういう文字列がパスとして表示されるか確認してみてください（説明/例示用にシングルクォーテーションで囲われているのなら勘違いです。ごめんなさい）。

set root_dir='c:\test'
echo %root_dir%DATA.SQL

たぶん、こう書かないとだめなんじゃないでしょうか

%root_dir%\DATA.SQL

あと、Output.csvがQutputになってますよ……

Comments

[maam]

ご回答ありがとうございます。
質問の説明用で、パスの中身や¥を省略していました…わかりにくくてすみません…
シングルクォーテーションについても同様です＞＜

[backyard]

了解です～