タグ: SQLServer

SQLServerはテーブル指定のバックアップができない？

ってなことで、エクスポートとインポートをお勉強。

1) Export

BCP {DBName}.{scheme}.{Table} out c:\table1.txt -c -S localhost -U {user} -P {pwd}
BCP "SELECT * FROM {DBName}.{scheme}.{Table} WHERE 1=1" queryout c:\table2.txt -c -S localhost -U {user} -P {pwd}

1つ目はテーブルそのまま。テーブル指定後は、「out」を指定して、エクスポート先ファイルを指定してExportする。
2つ目は条件句で範囲を指定してその分だけデータをとってくる方法。こっちは「queryout」でExportする。
いずれも、その後ろは接続先とユーザ名・パスワードを設定。

2) Import

BCP {DBName}.{scheme}.{Table} IN c:\table1.txt -c -S  localhost -U {user} -P {pwd}

インポート先を指定後、「IN」でインポート元ファイルを指定。
こちらもインポート先のDB接続情報を後ろに入れる。
挿入するので、主キーが重複しないようあらかじめ削除しておくこと。

モチの論、BCP.exeがないと動きまヘン。

SQLServerはそのDB設定によって、大文字小文字や半角全角を意識しないとのこと。
つまり、’A’ = ’a’ は True になるわけ。
現在の設定を確認したい場合は、以下のようなSELECT文を実行する。
（{DBNAME}のところに対象DB名を入れる）

SELECT DATABASEPROPERTYEX('{DBNAME}', 'collation');

これはこれでいいんだが、で、意識したいときはどうすんの？って話で、

SELECT * 
FROM foo
WHERE x = 'X' COLLATE Japanese_CS_AS
AND   y = 'Y' COLLATE Japanese_BIN;

と、まぁこんな風に条件項目ごとに設定をしていくとのこと。

BINやCS、AS、KS等の違いは以下参照。
Windows 照合順序並べ替えスタイル

さて、最近くだらないことをまじめに書いているので、久しぶりに技術的なことを軽くメモ書きしておこう。

8桁数値を日付(yyyy/mm/dd)表示にする方法

SELECT CONVERT(varchar,CONVERT(datetime, CONVERT(varchar,20160223), 112),111)

1つ目(内側から)は文字列に変換、2つ目のCONVERTではDATETIME型に変換するので、出力としては「2016/02/23 0:00:00」というように日付と時間で出力される。
これを「yyyy/mm/dd」形式の文字列で出力させるために3つ目(一番外側)のCONVERTを用意。

これとは逆に日付を8桁数値に変える場合は、データを「yyyymmdd」の文字列で出力した後、INT(またはNUMERIC)型に変えてしまえばよい。

SELECT CONVERT(int,CONVERT(varchar, '2016-2-14', 112))

カンマ区切りのデータを縦に並べるプロシージャを作ってみた。

CREATE PROCEDURE csvToRows
    @csv VARCHAR(MAX)
AS
BEGIN

    DECLARE @exit_flg       TINYINT       = 0
    DECLARE @i              INT           = 1
    DECLARE @imax           INT
    DECLARE @buf            VARCHAR(MAX)
        
    BEGIN TRY
        -- はじめにNULLや空白を回避
        IF RTRIM(LTRIM(ISNULL(@csv,'')))='' 
        BEGIN
            RAISERROR (N'NULLはダメ！', 18, 1)
        END

        CREATE TABLE #w_temp (
            buf            VARCHAR(MAX)
        )
        
        SET @buf = @csv
        WHILE @exit_flg = 0
        BEGIN
            SET @buf = SUBSTRING(@buf, @i  , LEN(@buf))
            SET @imax = CHARINDEX(',', @buf)
            IF @imax = 0
            BEGIN
                INSERT INTO #w_temp
                SELECT SUBSTRING(@buf, 1, LEN(@buf)) 
                SET @exit_flg = 1
            END
            ELSE
            BEGIN
                INSERT INTO #w_temp
                SELECT SUBSTRING(@buf, 1, @imax - 1) 
                SET @i = @imax + 1
            END
        END
        SELECT 'OK' AS result, NULL AS err_msg, buf FROM #w_temp

    END TRY
    BEGIN CATCH
        
        SELECT 'NG' AS result, ERROR_MESSAGE() AS err_msg
        
    END CATCH
END

「SQLSeverの2014ぐらいでないとTHROWが使えない」ってことで、RAISERRORでエラーを発生させてCATCH句に強制的に移動するように。

したはずなんだけど、いかないのは何故？

って、原因は重要度に設定する値にあった。

重要度＝1～10の場合、CATCH句へ飛ばない。

RAISERROR (N'エラーメッセージ', 10, 1);

重要度＝11～18の場合、CATCH句へ飛び、18までなら普通のユーザでも指定できる。

RAISERROR (N'エラーメッセージ', 18, 1);

重要度＝19～の場合、sysadminとか権限のあるユーザでないと、そもそも設定できない。

RAISERROR (N'エラーメッセージ', 19, 1);

ということで、11～18の値を設定することで、無事CATCH句に飛びましたとさ！

ストアドプロシージャ内で、別のストアドプロシージャを呼び出す方法。

例えば、proc_GetEmployeeIn(引数:@bumon_cd) を呼び出して結果を取得する場合だと、
以下のように、あらかじめワークテーブルを作っておいてから、
INSERT INTO ワークテーブル EXEC プロシージャ名 引数1, 引数2 , …
というようになる。

CREATE TABLE #work_table (
      emp_cd VARCHAR(1000)
    , emp_name VARCHAR(10)
)
INSERT INTO #work_table EXEC proc_GetEmployeeIn @bumon_cd

SQLServerで縦並びになっているデータを横並び（カンマ区切り）にして出力したくなった。

【TABLE1】
CD | KOMOKU
1 | A
1 | B
2 | C
2 | D
【TABLE2】
CD | CNAME
1 | あ
2 | い

ってあった場合、

CD | CNAME | KOMOKU
1 | あ | A,B
2 | い | C,D

って出したい！ってことで、調べた結果、

select 
 TABLE2.*
 ,replace((select KOMOKU  AS [data()] 
 from TABLE1
 where (TABLE1.CD=TABLE2.CD)
 for xml path('')
 ),' ',',') AS KOMOKU
 from TABLE2

ってな感じに記述するとのこと。
KOMOKUにスペースが含まれているとこれってうまくいかないんだよねぇ・・・とか思いつつ、自分の場合は特に問題なかったのでOK。
[data()] ってのがアトミック値にするっていうことらしいがよくわからんかった。
（分解できないとか、処理速度が速いとか書いてあったけど、結局よくわからん・・・）

Excelのデータを1行ずつ挿入していくのが面倒なので、シートまるっとINSERTをやってみた。

引数は以下の通り
workbook_path：元データのブック
sheet_name：workbook_pathのどのシートを元データとするのか指定
db_src：DBの場所
db_name：DBの名前
login_id：DBのユーザID
login_pw：DBのユーザパスワード
table_name：DBのどのテーブルを操作するのか（ここではデータの投入先）

Private Function ExportFromXLSX(workbook_path as String,sheet_name as String, db_src as String, db_name as String,login_id as String, login_pw as String, table_name as String) As Boolean

	Dim cn as ADODB.Connection
	Dim recs_aff as Long

	On Error Goto ErrFunc
	
	Set cn = New ADODB.Connection

	'2007バージョンのExcelファイルを開く
	cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & workbook_path & ";" & _
	        "Extended Properties=Excel 12.0"

	'Jetプロバイダを使用してインポート
	sql = "INSERT INTO [odbc;Driver={SQL Server};" & _
						"Server=" & db_src & ";" _ 
						& "Database=" & db_name & ";"  _
						& "UID=" & login_id  _
						& "PWD=" & login_pw _
						& "]." & table_name _
						& " SELECT * FROM [" & sheet_name & "$]"
	    
	cn.Execute sql, recs_aff, adExecuteNoRecords
	cn.Close
	Set cn = Nothing

	ExportFromXLSX = True
	Exit Function
	
ErrFunc:
	
	'エラーメッセージ表示
	' ---- (省略) ----
	
	If Not cn Is Nothing Then
		If cn.State=1 Then cn.Close
		Set cn = Nothing
	End If
	
End Function

こんなことやらなきゃいけないのは、SQLServer側からExcelシートの内容をとってこようとしたときに、分散クエリほにゃほにゃ・・・というエラーが出てしまったからなのだ。
なのでExcelシートから、SQLServer側に送るイメージ。

前の投稿から結構日数が経ってしまった。
というか、１月以上あいてしまった。

最近、SQLServerに接続してうんたらかんたら・・・というような開発をしている。
面倒だ・・・

DBによって、いろいろ関数がちがうのが困る。
例えば、OracleとかPostgreSQLだと、Now()で取ってこれる現在日時も、SQLServerだとGetDate()。
PostgreSQLだと、カラム名::intとかすればキャストできてしまうわけだが、SQLServerだと、Cast関数か、Convert関数を使う。

どっちが良いかはともかく、揃えてくれ・・・と思う。

さて、日付を8桁のYYYYMMDD形式にする方法。
現在時刻を8桁の文字列にする方法(CONVERT関数)
convert(varchar(8), getdate(), 112)

引数の最後の112ってのが「yyyymmdd」形式という意味。
これが、111だと「yyyy/mm/dd」、110だと「yyyy-mm-dd」になる。
（始めの引数をvarchar(10)にしておかねば・・・）

文字を数値に(CONVERT関数)
convert(int, ‘1234’)

数値を文字に(STR関数)
str(1234)

VBAでADOを利用しているが、数値のままだと文字連結でエラーになってしまった。
なので文字列型にしてから連結するためにSTR関数を使う。

ネットでいろいろ調べてようやく参照できるようになったので、メモ。
PostgreSQLのODBC Driverはすでにインストール済みとして書いてるよ。

まずは、コンパネ→管理ツール→Microsoft ODBC アドミニストレータで、システムDNSにODBCの接続設定を追加する。
（ユーザーDNSじゃないところがポイント）

次に、SQLServerを開いて、新しいリンクサーバーを作成する。
「全般」ページで以下を設定。
　サーバの種類はその他のデータベース。
　プロバイダは Microsoft OLE DB Provider ODBC Drivers。
　製品名はPostgreSQL。
　データソースは、最初に作成したODBC接続設定の名称。

「セキュリティ」ページで以下を設定。
　このセキュリティコンテキストを使用するを選択して、
　リモートログインとパスワードにPostgreSQLのDBのユーザー名とパスワードを入力。

これで、OKボタンを押せば、リンクサーバが出来上がり。

あとの注意点は、Select文は、OPENQUERYを使うというところ。

SELECT * FROM OPENQUERY([リンクサーバ名],’SELECT * FROM [スキーマ名(public)].[テーブル名]’)

といった感じ。

丸一日かかって、OracleとPostgreSQL両方のDBを参照するやり方がわかった。
やっぱり、勉強が足りないな。

[追記：64bit版OS利用時(WindowsServerとか)]
64bit版OSの場合は、PostgreSQLのODBC Driver(64bit版)をインストールすれば、あとは同様に処理ができるようである。

他のサイトでもこの手の記事は多いんだけど、設定のテキストボックスに何を入れていいかまで書いてないのだ。
ここまで書いてもらえないと、応用力がないのでわからんのよ、私は。