SQL Serverでは、異なるデータベースエンジン間でのデータ移行をTransact-SQLだけで実現可能です。ここでは、実務で頻繁に使用する手法を体系的に解説します。

1. SQL Server間でのデータ操作

1.1 SELECT INTOによるテーブル作成とデータコピー

SELECT INTO文は、データ抽出と同時に新規テーブルを作成できる機能を持っています。このコマンドは、指定されたSELECTクエリの結果に基づいて的空テーブルを生成し、抽出したデータを自動的に挿入します。注意点として、INTO句で指定したテーブル名が既に存在する場合はエラーになるため、既存のテーブルを誤って上書きする心配はありません。 具体的な使用例として、employeeテーブル（フィールドはid INT型、name VARCHAR(50)型）を別のテーブルとして複製する場合を考えます。

SELECT * INTO employee_backup FROM employee

このSQLを実行すると、employee_backupという名前の空テーブルが自動的に作成され、employeeテーブルの全データが挿入されます。すべてのフィールドをコピーせず、特定のフィールドのみを複製したい場合は、プレースホルダー（*）の代わりにフィールド名を指定してください。 同一データベース内뿐만 아니라、異なるSQL Serverインスタンス間でもテーブルを作成できます。

USE sales
SELECT * INTO inventory.dbo.department FROM employee

この例では、salesデータベースからinventoryデータベースのdepartmentテーブルを作成します。実行には、対象データベースでのテーブル作成権限が必要です。 重要な制限事項として、COMPUTE句との併用は 불가능입니다。COMPUTEは集約結果を返すため、ベーステーブルの構造を特定できないためです。

1.2 INSERT INTOとUPDATEによるデータ操作

SELECT INTOは新規テーブル作成専用ですが、INSERT INTOを使用すると既存のテーブルにデータを追加できます。

INSERT INTO employee SELECT * FROM employee_temp
-- または
INSERT INTO inventory.dbo.department SELECT * FROM employee_temp

主キー重複によるエラーが発生する可能性があります。その場合は、NOT EXISTS句を使用して重複除外する必要があります。

INSERT INTO employee
SELECT * FROM employee_temp
WHERE NOT EXISTS(
    SELECT employee.id FROM employee 
    WHERE employee.id = employee_temp.id
)

このスクリプトは、employee_temp表中いでemployeeテーブルに存在しないレコードのみを挿入します。 既存レコードの更新にはUPDATE句を使用します。

UPDATE employee 
SET employee.name = employee_temp.name, 
    employee.department = employee_temp.department 
FROM employee_temp
WHERE employee.id = employee_temp.id

このUPDATEとINSERTを組み合わせることで、UPSERT（挿入または更新）機能を実装できます。ただし、UPDATEを先に実行し、その後INSERTを実行するように順序を意識する必要があります。

2. 異種データベース間のデータ移行

2.1 OPENDATASOURCEとOPENROWSETの概要

SQL Serverは、OPENDATASOURCEおよびOPENROWSET関数を通じて、OLE DB対応であれば任意のデータベースにアクセスできます。これらの関数により、SQL Serverから直接Access、Excel、CSV、dBase、FoxProなどの外部データソースを操作可能です。 OPENDATASOURCEは特定のデータソースへの接続を確立し、SELECT、UPDATE、INSERT、DELETEの各文のテーブル参照として機能します。

SELECT * FROM
OPENDATASOURCE('SQLOLEDB', 'Data Source=192.168.100.50;User ID=admin;Password=pass123').pubs.dbo.authors

このクエリは、192.168.100.50サーバー上のpubsデータベースのauthorsテーブルを参照します。OPENDATASOURCEは2つのパラメータを取り、最初のprovider_nameはOLE DBプロバイダーを識別し、2番目のconnection_stringはデータソース固有の接続情報で構成されます。使用するプロバイダーの接続文字列が不明な場合は、Visual Studioなどの開発ツールのADOコンポーネントが自動生成する接続情報を参照できます。 OPENROWSETは接続確立とクエリ実行を1つの関数で実行でき、よりシンプルな構文を提供します。

OPENROWSET('MSDASQL.1', 'Driver=Microsoft Visual FoxPro Driver; SourceDB=c:\db; SourceType=DBF', 'SELECT * FROM [customer.dbf]')

2.2 SQL Server間での双方向データ移行

【データ取り込み例】

SELECT * INTO authors_copy
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=192.168.100.50;User ID=admin;Password=pass123'
).pubs.dbo.authors

【データエクスポート例】

INSERT INTO OPENDATASOURCE(
'SQLOLEDB',
'Data Source=192.168.100.50;User ID=admin;Password=pass123'
).test.dbo.authors 
SELECT * FROM pubs.dbo.authors

OPENROWSETを使用した同等の操作は次の通りです。

INSERT INTO OPENROWSET('SQLOLEDB','192.168.100.50';'admin';'pass123', 'select * from test.dbo.kk')
SELECT * FROM pubs.dbo.authors

OPENROWSETの接続文字列では、パラメータ区切りにカンマではなくセミコロンを使用する点に注意が必要です。両関数とも変数を受け付けません。

2.3 SQL ServerとAccess間のデータ連携

【AccessからSQL Serverへの取り込み】

SELECT * INTO access_table
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\backup\data.mdb;Persist Security Info=False')...users

OPENROWSETを使用した場合は次のようになります。

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\backup\data.mdb';'admin';'','SELECT * FROM users')

【SQL ServerからAccessへのエクスポート】

INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\backup\data.mdb;Persist Security Info=False')...users
SELECT * FROM access_table

Access接続にはMicrosoft.Jet.OLEDB.4.0プロバイダーを使用します。OPENDATASOURCEで外部データベースのテーブルを参照する場合、ドット（.）の代わりに三点リーダー（...）を使用する点がSQL Server間接続とは異なります。

2.4 SQL Serverとテキストファイル間のデータ連携

【テキストファイルからSQL Serverへの取り込み】

SELECT * INTO textdata FROM
OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','Text;DATABASE=c:\export\')...[records#csv]

【SQL Serverからテキストファイルへのエクスポート】

INSERT INTO OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','Text;DATABASE=c:\export\')...[records#csv] 
SELECT * FROM textdata

OPENROWSETを使用した場合は以下の通りです。

INSERT INTO OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;DATABASE=c:\export\', [records#csv]) 
SELECT * FROM textdata

特定のフィールドのみをエクスポートする場合は、次のように記述します。

INSERT INTO OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;DATABASE=c:\export\', 'SELECT name FROM [records#csv]') 
SELECT name FROM textdata

ファイル名の拡張子前的ドット（.）は番号記号（#）で置き換える必要があります。エクスポート先のテキストファイルは事前に存在している必要があり、ヘッダー行はインポート先テーブルのフィールド定義と一致している必要があります。

2.5 SQL ServerとdBase形式間のデータ連携

【dBaseからSQL Serverへの取り込み】

SELECT * INTO dbase_data FROM
OPENROWSET('MICROSOFT.JET.OLEDB.4.0' , 'dBase III;HDR=NO;IMEX=2;DATABASE=C:\datafolder\','SELECT * FROM [products.dbf]')

【SQL ServerからdBaseへのエクスポート】

INSERT INTO OPENROWSET('MICROSOFT.JET.OLEDB.4.0' , 'dBase III;HDR=NO;IMEX=2;DATABASE=C:\datafolder\','SELECT * FROM [products.dbf]')
SELECT * FROM dbase_data

ファイル名を角括弧で囲むのは、ファイル名にスペースや特殊文字が含まれる場合のエラーを防ぐためです。通常のファイル名の場合は省略できます。

2.6 SQL ServerとFoxPro形式間のデータ連携

【FoxProからSQL Serverへの取り込み】

SELECT * INTO foxpro_data FROM OPENROWSET('MSDASQL.1',  
'Driver=Microsoft Visual FoxPro Driver; SourceDB=c:\foxdata\; SourceType=DBF', 'SELECT * FROM [clients.dbf]')

【SQL ServerからFoxProへのエクスポート】

INSERT INTO OPENROWSET('MSDASQL.1' , 'Driver=Microsoft Visual FoxPro Driver; SourceDB=c:\foxdata\; SourceType=DBF','SELECT * FROM clients.dbf') 
SELECT * FROM foxpro_data

FoxPro Driverを使用する場合、角括弧で囲まないでください。Driverの仕様により、エラーが発生します。

2.7 SQL ServerとExcelファイル間のデータ連携

【ExcelからSQL Serverへの取り込み】

SELECT * INTO excel_data FROM OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0',
'Excel 5.0;DATABASE=c:\reports\sales.xlsx' )...[Sheet1$]

【SQL ServerからExcelへのエクスポート】

INSERT INTO OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0',
'Excel 5.0;DATABASE=c:\reports\sales.xlsx' )...[Sheet1$] 
SELECT * FROM excel_data

インポート先のExcelシートには、対象テーブルのフィールドに対応する列が必要です。対応していない列が存在すると、エラーが発生します。

3. リンクサーバー機能による永続的接続

sp_addlinkedserverストアドプロシージャを使用すると、外部データベースをSQL Serverに登録でき通常のテーブルように参照が可能になります。

EXEC sp_addlinkedserver
'msaccess',
'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0',
'c:\backup\archive.mdb'

登録後、次のようにクエリを実行できます。

SELECT * FROM msaccess...users

データ取り込みも容易です。

SELECT * INTO local_users FROM msaccess...users

登録を解除する場合は、sp_dropserverを使用します。

EXEC sp_dropserver 'msaccess'

4. 異種データベース間の直接データ移行

TRANSACT-SQLを使用すると、SQL Serverを経由して任意のデータソース間で直接データ移行できます。 AccessからExcelへの直接エクスポートの例：

INSERT INTO OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0',
'Excel 5.0;DATABASE=c:\reports\sales.xlsx' )...[Sheet1$] 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'c:\backup\archive.mdb';'admin';'','SELECT * FROM master_data')

この手法により、Accessのmaster_dataテーブルからExcelファイル内のSheet1に直接データが書き込まれます。

まとめ

TRANSACT-SQLによるデータ移行機能は 매우强大であり、ADO.NETやODBCを使用したプログラムと連携させることで、カスタムのデータ移行ツールを簡単に構築できます。