SQLSERVERにAccessテーブルをストアドでインポート

シリーズ目次

設計：AccessのデータをSQLSERVER(MSSQL)でコンバートした話

その1：SQLSERVERにAccessテーブルをストアドでインポート

その2：SQLSERVERで全角半角変換するストアド(C#なDLLを使用)

その3：PowerShellでSelect結果をCSV出力
その4：PowerShellでテーブルCreateスクリプトを出力

前置き

今回は、AccessテーブルをSQLSERVER(MSSQL)にストアドでインポートする方法を紹介します。

必要なインストール

• SQLSERVER：Expressでいいです。
• SQLSERVERManagementStudio(SSMS)：SQLクライアントならなんでも行けそうなのですが、SQLSERVER扱うならこれが安パイ。デバッグ機能使いたかったら、18.0未満を使いましょう。最新版では廃止されていてすごい使いづらいです。
• Microsoft.ACE.OLEDB.12.0(Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント)：16.0もありますが、なぜかちゃんと配布されていないので今回はこちらを使います。

重要なのは、動作bitをすべてそろえてインストールすることです。

ここでは64bitで構成します。

MSさんのリンク先はころころ変わるので、各自最新のリンクをググって使用してください。

機能を有効にする

	-- データベースmasterに切り替え
	USE master
	-- sp_configure システム ストアド プロシージャの拡張オプションを表示
	EXEC sp_configure 'show advanced options', 1;
	RECONFIGURE;
	-- アドホック分散クエリを有効
	EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
	RECONFIGURE;
	-- Microsoft.ACE.OLEDB.12.0の有効化
	EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess', 1;
	RECONFIGURE;
	EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters', 1;
	RECONFIGURE;

view raw setting_access.sql hosted with ❤ by GitHub

詳しくは下記URLドキュメントを参照

Excel から SQL Server または Azure SQL Database にデータをインポートする

ストアドの構成

持たせる機能は

• インポートするテーブルの一覧(wk_import_table_names)を取得
• インポート対象テーブルのみインポート

wk_import_table_names

で、ストアドは以下の通り

	CREATE PROCEDURE [dbo].[IMPORT_Access]
	@returnCode INT OUTPUT,
	@returnMessage VARCHAR(MAX) OUTPUT,
	@returnTableName VARCHAR(MAX) OUTPUT
	AS
	BEGIN
	SET NOCOUNT ON;
	DECLARE @SQL NVARCHAR(1000);
	SET @SQL = '';
	--import_table_namesにあるテーブルをすべてインポート
	--カーソル定義
	DECLARE @tableName nvarchar(50)
	DECLARE @accessName nvarchar(50)
	DECLARE cursorTableNames CURSOR FOR
	SELECT
	import_table_name
	,source_access_name
	FROM
	wk_import_table_names
	--カーソルオープン
	OPEN cursorTableNames;
	--カーソルのフェッチ
	FETCH NEXT FROM cursorTableNames
	INTO @tableName,@accessName
	WHILE @@FETCH_STATUS = 0
	BEGIN
	BEGIN TRY
	--既存ワークテーブルの削除
	SET @SQL = 'DROP TABLE IF EXISTS ' + @tableName + ';';
	EXECUTE sp_executesql @SQL;
	--インポート元Accessの分岐
	SET @SQL = 'SELECT * INTO ' + @tableName + ' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''C:\Access\test.accdb'';''admin'';'''' , ''SELECT * FROM ' + @tableName + ''')';
	--インポートの実行
	EXECUTE sp_executesql @SQL;
	PRINT @tableName + ' インポート END'
	--次のカーソルをフェッチ
	FETCH NEXT FROM cursorTableNames
	INTO @tableName,@accessName
	END TRY
	BEGIN CATCH
	SET @returnCode = ERROR_NUMBER();
	SET @returnMessage = ERROR_MESSAGE();
	SET @returnTableName = @tableName;
	BREAK;
	END CATCH;
	END
	END

view raw IMPORT_Access.sql hosted with ❤ by GitHub

このOPENROWSETってやつで、有効化したOLEを指定して、Accessのデータベースに接続します。
「import_table_name」にAccessのテーブル名を入れておけば、同名のテーブルをSQLSERVERに作成できる仕組みです。
もちろん単にSELECTすることもできます。実はExcelもインポートできます。

ね？簡単でしょう？
OPENROWSETについて詳しくなりたい方は下記参照
OPENROWSET (Transact-SQL)
BULK INSERT または OPENROWSET(BULK...) を使用して SQL Server にデータをインポートする

データ型が想定通りに入らない場合は、FORMATFILEを使用して型を指定することもできるようです。
データの一括インポートでのフォーマット ファイルの使用 (SQL Server)
今回は使用しなかったのですが、例えばCSVのインポートを行うときなんかには使えそうです。どこかで検証してみたいです。

ということで今回は「SQLSERVERにAccessテーブルをストアドでインポート」するでした～

次回

その2：SQLSERVERで全角半角変換するストアド(C#なDLLを使用)