【SQL Server(連載)】外部ファイルの読み込み -44-

www.pon-x.jp

前回の続き

今回は、外部ファイルを読み込む方法を紹介します。

BULK INSERT

SQLSeverで外部ファイルを取り込む方法としての王道はBULKINSERTです。
では、さっそくやってみます。

BULK INSERT [テーブル名]
FROM 'C:\Users\***\TEST.CSV'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

解説します。

BULK INSERT [テーブル名]

取り込むテーブルを指定します。

FROM 'C:\Users\***\TEST.CSV'

取り込むファイルを指定します。

FIELDTERMINATOR = ',',

今回はカンマ区切りのCSVファイルなので、カンマで区切られているということを指定する。

ROWTERMINATOR = '\n'

改行コードを指定します。\nや\r\nなどの場合があるので、テキストエディタなどで確認してみましょう。

WITHオプション

WITHのカッコ内でオプションを指定することで、細かい制御が可能です。

このページがよくまとめてあるので、参考になると思います。

www.pasokuma.net

以上、BULKINSERTを一時テーブルに入れてあげて、カーソルで回してあげて処理するなんて応用も考えられますね！！
今回はここまで。

【SQL Server(連載)】日付型の続き -43-

www.pon-x.jp

前回の続き

今回は、前回お伝え出来なかった日付型の内容の続きを紹介します。

DATEDIFF

２つの日付間の間隔をとることができます。
以外に結構使うんですよね。

年、月、日、時、分、秒ごとに計測してみます。

DECLARE @DATE01 DATETIME = '2021-01-13 10:10:10.010',
        @DATE02 DATETIME = '2023-04-01 00:00:00.000';


SELECT  DATEDIFF(yy, @DATE01, @DATE02) AS yy,
        DATEDIFF(mm, @DATE01, @DATE02) AS mm,
        DATEDIFF(dd, @DATE01, @DATE02) AS dd,
        DATEDIFF(hh, @DATE01, @DATE02) AS hh,
        DATEDIFF(mi, @DATE01, @DATE02) AS mi,
        DATEDIFF(ss, @DATE01, @DATE02) AS ss

こんなかんじです。

ISDATE

データが、DATE型なのかを確認します。
さまざまな形式を確かめてみましょう。

DECLARE @DATE01 VARCHAR(30) = '2021-01-13',
        @DATE02 VARCHAR(30) = '01-13-2021',
        @DATE03 VARCHAR(30) = '2021-01-13 00:00:00:000',
        @DATE04 VARCHAR(30) = 123456,
        @DATE05 VARCHAR(30) = '123456';

SELECT  ISDATE(@DATE01) AS '2021-01-13',
        ISDATE(@DATE02) AS '01-13-2021',
        ISDATE(@DATE03) AS '2021-01-13 00:00:00:000',
        ISDATE(@DATE04) AS _INT,
        ISDATE(@DATE05) AS _CHR

こんなかんじです。

文字型を日付型として扱うときにはISDATEで確認してから、DATEADDとかDATEDIFFとかやればエラーなく行けそうですね。

【SQL Server(連載)】日付型 -42-

www.pon-x.jp

今回は、日付型の扱い方を紹介していきます。

CONVERT関数

CONVERT関数は、データ型の変更を行ってくれます。
例えばこんな感じ。

 SELECT 
    GETDATE(),
    CONVERT(NVARCHAR, GETDATE())

GETDATE()は、現時点の日時を取得してくれるもので、CONVERT(NVARCHAR, GETDATE())は、現在日時を文字型に変換したということになります。

国ごとの表記法

年月日は国ごとに表記法が異なります。
そこで、CONVERT関数の第3引数に指定することによって制御可能です。
例えばこんな感じ

DECLARE @Date DATETIME = GETDATE();

SELECT   
         CONVERT(NVARCHAR, @Date, 101) AS '米国4桁',
         CONVERT(NVARCHAR, @Date, 1) AS '米国2桁',
         CONVERT(NVARCHAR, @Date, 111) AS '日本4桁',
         CONVERT(NVARCHAR, @Date, 11) AS '日本2桁'

他の国など詳しくは以下を見てみよう。

docs.microsoft.com

日時の加減

基準となる日から、日にちや時間をずらすことも可能だ。
例えば、本日から日にちをずらしてみる。

DECLARE @Today DATETIME = GETDATE()
DECLARE @Day_01 DATETIME = dateadd(DAY,1,GETDATE())
DECLARE @Week_01 DATETIME = dateadd(WEEK,-1,GETDATE())
DECLARE @Month_01 DATETIME = dateadd(MONTH,1,GETDATE())
DECLARE @Quarter_01 DATETIME = dateadd(QUARTER,-1,GETDATE())
DECLARE @Year_01 DATETIME = dateadd(YEAR,10,GETDATE())

SELECT 
    CONVERT(NVARCHAR, @Today, 111) AS '本日',
    CONVERT(NVARCHAR, @Day_01, 111) AS '1日後',
    CONVERT(NVARCHAR, @WEEK_01, 111) AS '1週前',
    CONVERT(NVARCHAR, @Month_01, 111) AS '1月後',
    CONVERT(NVARCHAR, @Quarter_01, 111) AS '3月前',
    CONVERT(NVARCHAR, @Year_10, 111) AS '10年後'

時間もこの通り

DECLARE @Today DATETIME = GETDATE()
DECLARE @Hour_01 DATETIME = dateadd(HOUR,1,GETDATE())
DECLARE @Minutie_01 DATETIME = dateadd(MINUTE,-1,GETDATE())
DECLARE @Second_01 DATETIME = dateadd(SECOND,1,GETDATE())


SELECT 
    @Today AS '現在',
    @Hour_01 AS '1時間後',
    @Minutie_01 AS '1分前',
    @Second_01 AS '1秒後'

以上今回はここまで。

【SQL Server(連載)】UNION -41-

www.pon-x.jp

今回はUNIONを紹介します。

UNION

簡単に言うと、行結合です。
2つのテーブルを行方向に結合してあげるためにUNIONを使います。

ためしに、Person.PersonテーブルのLastNameを結合してみます。
ただし、WHERE句でPersonTypeを抽出条件として指定しています。

SELECT LastName FROM Person.Person WHERE PersonType = 'EM'

UNION

SELECT LastName FROM Person.Person WHERE PersonType = 'IN'

570件のデータが確認されます。
実は、UNION とだけ指定すると、重複する行は削除されてユニークな値のみが出力されるのです。

UNION ALL

では、重複削除を行わず単純にテーブルを行結合するにはどうすればよいかというと、UNIONのあとにALLを付けます。
こんなかんじです。

SELECT LastName FROM Person.Person WHERE PersonType = 'EM'

UNION ALL

SELECT LastName FROM Person.Person WHERE PersonType = 'IN'

18757件と結果が全然違います。

注意

テーブル同士を行結合するのですから、カラム数は一致していなくてはなりません。 例えばこれはエラーになります。

SELECT LastName FROM Person.Person WHERE PersonType = 'EM'

UNION ALL

SELECT LastName,FirstName FROM Person.Person WHERE PersonType = 'IN'

こんなかんじです。




データ型の違いもダメです。

SELECT LastName FROM Person.Person WHERE PersonType = 'EM'

UNION ALL

SELECT BusinessEntityID FROM Person.Person WHERE PersonType = 'IN'

エラーになります。

以上、今回はここまで。

【SQL Server(連載)】WITH -40-

www.pon-x.jp

あけましておめでとうございます。今年もプログラ生活をよろしくお願いいたします。

さて、今年一発目はWITHの使い方です。

WITH

結構前に、サブクエリの話をしました。

www.pon-x.jp

WITHはこのようなサブクエリを可読性良く書けるのが特徴です。
早速書いてみよう。

上記の過去記事のSQLはこんなかんじでした。

SELECT * 
FROM 
    (
        SELECT 
            PersonType,
            COUNT(BusinessEntityID) AS CNT
        FROM Person.Person
        WHERE MiddleName IS NOT NULL
        GROUP BY PersonType
    ) TBL1
WHERE CNT > 100

これの出力はこんなかんじでした。

これをWITHを使って書いてみます。

WITH TBL AS (SELECT 
            PersonType,
            COUNT(BusinessEntityID) AS CNT
        FROM Person.Person
        WHERE MiddleName IS NOT NULL
        GROUP BY PersonType )


SELECT * FROM TBL WHERE CNT > 100

こんなかんじです。

サブクエリとしていた部分を分離しているようにかけているので、見やすくなりましたね。

複数定義することも可能です。
適当に、WITHで定義したテーブルを内部結合してみます。

WITH TBL1 AS (
        SELECT 
            *
        FROM Person.Person
        )
,TBL2 AS (
        SELECT * 
        FROM Person.Password
    )


SELECT * 
FROM TBL1 T1
    INNER JOIN TBL2 T2
        ON T1.BusinessEntityID = T2.BusinessEntityID

こんなかんじに、可読性を考えてあげるとメンテナンスもしやすくなるので積極的に使っていきたいですね。

【SQL Server(連載)】トランザクションの実装 その2 -39-

www.pon-x.jp

前回の続き

前回から、トランザクションの実装をしてますが、今回はエラーを検知してロールバックしてみたいと思います。

エラー検知

エラー検知はこのような構文です。

BEGIN TRY

    -- ###############################
    -- ここでエラーが発生したらCATCHに飛ばす
    -- ###############################

END TRY

BEGIN CATCH

    -- ###############################
    -- エラーがあったらここの内容を処理する
    -- ###############################

END CATCH

こんな感じです。

前回の内容に組み込んでみる。

では、早速やってみましょう。

IF OBJECT_ID(N'tempdb..#TEMP', N'U') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP
(
    NUM BIGINT,
    TXT NVARCHAR(10)
)


BEGIN TRY

    BEGIN TRANSACTION


        DECLARE @CNT INT

        SET @CNT = 0

        WHILE @CNT <= 30
            BEGIN
                INSERT INTO #TEMP
                VALUES(
                    POWER(2, @CNT),
                    '2^' + CONVERT(VARCHAR, @CNT)
                )

                IF @CNT = 20
                    SET @CNT = @CNT / 0

                SET @CNT += 1
            END


    COMMIT TRANSACTION
END TRY

BEGIN CATCH

    ROLLBACK TRANSACTION

END CATCH

SELECT * FROM #TEMP

上記の内容は、WHILEのループ内で@CNTが20の時に、わざとエラーを発生させています。
エラーをキャッチしてロールバックしているので、＃TEMPはからになって、最後に出力されます。
こんな感じです。

ロールバックしないで、エラーが出るまで処理をする

通常、エラーがでたら処理がとまってしまうのですが、TRY～CATCHはうまくエラーをハンドリングすることができます。
では、エラーがでるまで処理するというよみましょう。

IF OBJECT_ID(N'tempdb..#TEMP', N'U') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP
(
    NUM BIGINT,
    TXT NVARCHAR(10)
)


BEGIN TRY

    BEGIN TRANSACTION


        DECLARE @CNT INT

        SET @CNT = 0

        WHILE @CNT <= 30
            BEGIN
                INSERT INTO #TEMP
                VALUES(
                    POWER(2, @CNT),
                    '2^' + CONVERT(VARCHAR, @CNT)
                )

                IF @CNT = 20
                    SET @CNT = @CNT / 0

                SET @CNT += 1
            END


    COMMIT TRANSACTION
END TRY

BEGIN CATCH

    PRINT('##### ERROR #####')
    PRINT('@CNT -> ' + CONVERT(VARCHAR, @CNT))
    PRINT('##### ERROR #####')
    RETURN

END CATCH

SELECT * FROM #TEMP

上記の内容は、WHILEのループ内で@CNTが20の時に、わざとエラーを発生させています。
@CNTを0で割ろうとしていますが、分母が0になる数は存在しないので、エラーになります。

こんなかんじの出力です。
@CNTが20でおわっているのが確認できますね。

BEGIN CATCH以下の内容を確認しておこう。

BEGIN CATCH

    PRINT('##### ERROR #####')
    PRINT('@CNT -> ' + CONVERT(VARCHAR, @CNT))
    PRINT('##### ERROR #####')
    RETURN

END CATCH

SELECT * FROM #TEMP

こんなかんじですが、RETURNで処理を終了しています。
つまり、それ以下のSELECT * FROM #TEMPは処理されないということになります。
SELECT * FROM #TEMPは、もしエラーが発生されなかったら処理されるといことになります。

こんなかんじで、エラーがあったときにそこで終わるのかどうかということも明示しておく必要があります。

以上、今回はここまで。S

【SQL Server(連載)】トランザクションの実装 その1 -38-

www.pon-x.jp

前回はトランザクションの意義について説明しました。
今回は実装してみたいと思います。

コミット

では、トランザクションで処理を行いコミットしてみたいと思います。
以下例文です。

IF OBJECT_ID(N'tempdb..#TEMP', N'U') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP
(
    NUM BIGINT,
    TXT NVARCHAR(10)
)


BEGIN TRANSACTION


    DECLARE @CNT INT

    SET @CNT = 0

    WHILE @CNT <= 30
        BEGIN
            INSERT INTO #TEMP
            VALUES(
                POWER(2, @CNT),
                '2^' + CONVERT(VARCHAR, @CNT)
            )

            SET @CNT += 1
        END


COMMIT TRANSACTION

SELECT * FROM #TEMP

こんな感じに出力されます。

BEGIN TRANSACTIONでトランザクションを開始して、COMMIT TRANSACTIONでコミットされるということです。

ロールバック

ではもし、COMMIT TRANSACTIONではなく、ROLLBACK TRANSACTIONだとどうなるでしょうか。

IF OBJECT_ID(N'tempdb..#TEMP', N'U') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP
(
    NUM BIGINT,
    TXT NVARCHAR(10)
)


BEGIN TRANSACTION


    DECLARE @CNT INT

    SET @CNT = 0

    WHILE @CNT <= 30
        BEGIN
            INSERT INTO #TEMP
            VALUES(
                POWER(2, @CNT),
                '2^' + CONVERT(VARCHAR, @CNT)
            )

            SET @CNT += 1
        END


ROLLBACK TRANSACTION

SELECT * FROM #TEMP

こんなかんじに、トランザクションを開始してそれ以降の処理を行いますが、ロールバックを行うので、最後はトランザクション処理の前に戻ってしまうので、SELECTで出力しても何も中身はありません。

以上、次回はエラーを検知してロールバック処理を行います。