Help us understand the problem. What is going on with this article?

SQL Server の一時テーブルをNode.jsから使う時の注意点

More than 1 year has passed since last update.

SQL Server にある一時テーブルは、CSVファイルなど外部にある情報を一時的に取り込んでSQLとしてDB内にあるデータと合わせて処理したい時に便利です。
Azure SQL Databaseでも利用可能であり、Blobストレージにあるファイルの取り込み先としても利用できます。

Node.jsでAzure Functionsの関数を実装している時に、SQL Serverのクライアントmssqlでハマったことについて書きます。

TLTR :clock10:

一時テーブルを作る時はCREATE文をqueryじゃなくて、batchを使う。
以上。

サンプルコード

素直に書くと動かない

SQL文の実行はqueryメソッドを使う。
だから、一時テーブルの作成もqueryメソッドで実行する。

sample.js
const sql = require('mssql');

(async () => {
    try {
        var connection = await sql.connect({
            user: '**',
            password: '**',
            server: '**',
            database: '**'
        })
        var request = connection.request();
        // Create a temporary table #tmp_users
        await request.query('CREATE TABLE #tmp_users (name NVARCHAR(100) NOT NULL)')

        const names = ['Su', 'Yui', 'Moa']
        names.forEach(async (name) => {
            await request.query(`INSERT INTO #tmp_users VALUES ('${name}')`)
        })

        const result = await request.query('SELECT * FROM #tmp_users')
        console.log(result)
    } catch(err) {
        console.error(err)
    }
})();

しかし、素直にqueryでSQL文を送ってみるも、期待どおりに動かない。どわーっとエラーが出て来るのだけど、肝心なところだけ見ると、どうやら作ったはずの一時テーブル#tmp_usersが無いようだ。

$ node sample.js
...
RequestError: Invalid object name '#tmp_users'.
...

動く

sample.js
const sql = require('mssql');

(async () => {
    try {
        var connection = await sql.connect({
            user: '**',
            password: '**',
            server: '**',
            database: '**'
        })
        var request = connection.request();
        // Create a temporary table #tmp_users
        await request.batch('CREATE TABLE #tmp_users (name NVARCHAR(100) NOT NULL)')

        const names = ['Su', 'Yui', 'Moa']
        names.forEach(async (name) => {
            await request.query(`INSERT INTO #tmp_users VALUES ('${name}')`)
        })

        const result = await request.query('SELECT * FROM #tmp_users')
        console.log(result)
    } catch(err) {
        console.error(err)
    }
})();

queryではなく、batchという別のメソッドを使うと期待した通りに動く。

何が起きているのか

色々調べてみたので、まとめてみる。

そもそも一時テーブルとは :clock1:

一時的な用途で使うためのテーブルのことで、作成時のセッションの中でだけ存在できる。
セッションを切った時に、SQL Serverによって自動的にDROP TABLEが実行される。
ローカル一時テーブルは、接続しているセッションの中でだけ参照できる。一方で、グローバルは他のセッションからでも参照ができるが、ローカルと同様に、その一時テーブルを作成したセッションを切った時に削除される。

で、Node.jsのクライアントmssqlquerybatchって何だ :question:

mssqlREADMEにbatchの記述があるので、そこを見ると。

Execute the SQL command. Unlike query, it doesn't use sp_executesql, so is not likely that SQL Server will reuse the execution plan it generates for the SQL.

とある。queryは内部でsp_executesqlという動的にSQLを実行するための、システムが予め持っているストアドプロシージャを使っているようだ。

sp_executesql に渡されるSQLは別セッションで実行される。

sp_executesqlと一時テーブルに関するこの記事によると、

Any dynamic SQL execution with either exec() or sp_executeSQL is ran in
a child session/scope of the current one.

ということで、異なるセッションで実行されるそうだ。

まとめ

  • ローカル一時テーブルはセッション内でしか存在できず、かつそのセッション内でしか参照できない
  • queryで実行されるSQLはそこまでのセッションとは異なるセッションで実行される。
  • すなわち、queryでローカル一時テーブルを作成しても、即座に消えてしまう。

ということがわかりました。つまり、ローカル一時テーブルを作成する時はbatchで実行しましょうということです :smile:

参考

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away