SQL ServerのSELECT結果をINSERTに変換!bcpコマンドとPowerShellも紹介
SQL ServerでSELECT結果をINSERTスクリプトに変換する方法
SQL ServerでSELECTクエリを実行した結果を、INSERTスクリプトに変換することは、データを別のテーブルに挿入したり、既存のデータを更新したりする際に役立ちます。
このチュートリアルでは、以下の2つの方法について説明します。
- SQL Server Management Studio (SSMS) を使用する
- T-SQLコードを使用する
方法1:SSMSを使用する
- SSMSでSELECTクエリを実行し、結果セットをグリッドに表示します。
- 右クリックメニューから「スクリプトとしてエクスポート」を選択します。
- 「INSERT TO」オプションを選択します。
- 挿入先のテーブルを選択します。
- 各列に対応するINSERTステートメントを確認し、必要に応じて編集します。
- 「スクリプトの実行」をクリックして、INSERTスクリプトを実行します。
- 以下のT-SQLコードを参考に、INSERTスクリプトを記述します。
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table;
target_table
を挿入先のテーブル名に置き換えます。column1, column2, ...
を挿入する列名に置き換えます。source_table
をSELECTクエリを実行する元のテーブル名に置き換えます。- 必要に応じて、WHERE句やORDER BY句を追加します。
- 上記のコードをクエリエディタに貼り付け、実行します。
例
以下の例では、source_table
のデータをtarget_table
に挿入します。
INSERT INTO target_table (customer_id, first_name, last_name)
SELECT customer_id, first_name, last_name
FROM source_table;
補足
- IDENTITY列に値を挿入する場合は、
INSERT INTO target_table (column1, column2, ...) OUTPUT INSERTED.identity_column
のように記述します。 - 主キー制約のあるテーブルに挿入する場合は、INSERTステートメントに
ON DUPLICATE KEY UPDATE
句を追加して、既存のレコードを更新することができます。
-- source_tableテーブルの構造
CREATE TABLE source_table (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
-- target_tableテーブルの構造
CREATE TABLE target_table (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
-- サンプルデータ
INSERT INTO source_table (customer_id, first_name, last_name)
VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Peter', 'Jones');
-- SELECT結果をINSERTスクリプトに変換
INSERT INTO target_table (customer_id, first_name, last_name)
SELECT customer_id, first_name, last_name
FROM source_table;
このスクリプトを実行すると、以下の結果になります。
-- target_tableテーブルの内容
customer_id | first_name | last_name
-----------+------------+-----------
1 | John | Doe
2 | Jane | Smith
3 | Peter | Jones
説明
- 上記のコードは、まず
source_table
とtarget_table
テーブルの構造を定義します。 - 次に、
source_table
テーブルにサンプルデータを入力します。 - 最後に、
SELECT
クエリを使用してsource_table
テーブルのデータを取得し、INSERT
ステートメントを使用してtarget_table
テーブルに挿入します。
- このコードはあくまで例であり、実際の状況に合わせて変更する必要があります。
概要
bcpコマンドは、SQL Serverのネイティブコマンドラインツールであり、データを高速かつ効率的にエクスポートおよびインポートすることができます。このツールを使用して、SELECTクエリの結果をINSERTスクリプトに変換することもできます。
手順
- コマンドプロンプトを開きます。
- 以下のコマンドを実行します。
bcp target_table OUT="INSERT.sql" -c -T -S server_name -U username -P password -q "SELECT * FROM source_table"
server_name
をSQL Serverのサーバー名に置き換えます。
- 上記のコマンドを実行すると、
INSERT.sql
という名前のファイルが作成されます。このファイルには、source_table
テーブルのデータをtarget_table
テーブルに挿入するINSERTスクリプトが含まれています。
bcp target_table OUT="INSERT.sql" -c -T -S localhost -U sa -P password123 -q "SELECT * FROM source_table"
-c
オプションは、列名をINSERTスクリプトに含めることを指定します。-T
オプションは、テキスト形式でデータを出力することを指定します。-S
オプションは、SQL Serverのサーバー名を指定します。-q
オプションは、実行するSQLクエリを指定します。
PowerShellを使用する
PowerShellは、タスクを自動化するための強力なスクリプティング言語です。この言語を使用して、SELECTクエリの結果をINSERTスクリプトに変換することもできます。
- PowerShell ISEを開きます。
$serverName = "localhost"
$username = "sa"
$password = "password123"
$sourceTable = "source_table"
$targetTable = "target_table"
$connectionString = "Server=$serverName;Database=master;Integrated Security=SSPI;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()
$sql = "SELECT * FROM $sourceTable"
$cmd = New-Object System.Data.SqlClient.SqlCommand($sql, $connection)
$reader = $cmd.ExecuteReader()
$insertScript = ""
while ($reader.Read()) {
$values = ""
for ($i = 0; $i -lt $reader.FieldCount; $i++) {
$value = $reader.GetValue($i)
if ($value -eq $null) {
$value = "NULL"
} else {
$value = "'" + $value.ToString() + "'"
}
if ($i -gt 0) {
$values += ", "
}
$values += $value
}
$insertScript += "INSERT INTO $targetTable VALUES ($values);\n"
}
$reader.Close()
$connection.Close()
Write-Output $insertScript
- 上記のスクリプトを実行すると、PowerShellコンソールにINSERTスクリプトが出力されます。
$serverName = "localhost"
$username = "sa"
$password = "password123"
$sourceTable = "source_table"
$targetTable = "target_table"
- このスクリプトは、
sql sql-server sql-server-2008