SQL初心者でも安心!MariaDBでテーブル間データ挿入を徹底解説
SQLを使って別のテーブルからテーブルにデータを挿入する方法(MariaDB)
MariaDBなどのSQLデータベースでは、INSERT INTO ... SELECT ...
構文を使って、別のテーブルからデータを1つのテーブルに挿入することができます。これは、データを複製したり、集計結果を新しいテーブルに保存したりする場合に役立ちます。
構文
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
[WHERE condition];
説明
target_table
: データを挿入するテーブルの名前column1, column2, ...
: 挿入する列の名前。カッコ内に指定しない場合は、すべての列にデータが挿入されます。[WHERE condition]
: 挿入するデータを選択するための条件式。オプションです。
例
以下の例では、customers
テーブルからname
とemail
列のデータを選択し、subscribers
テーブルに挿入します。
INSERT INTO subscribers (name, email)
SELECT name, email
FROM customers;
補足
- 挿入する列と、SELECT句で選択する列の順序が一致している必要があります。
- 主キーの制約がある列にデータを挿入する場合は、重複データがないように注意する必要があります。
INSERT INTO ... SELECT ...
構文は、INSERT INTO ... VALUES ...構文よりも効率的にデータ挿入できます。
上記以外にも、UNION
やJOIN
などの構文を使って、複数のテーブルからデータを1つのテーブルに挿入する方法があります。
Example 1: Inserting all columns from one table to another
INSERT INTO target_table
SELECT *
FROM source_table;
INSERT INTO target_table (column1, column2)
SELECT column1, column2
FROM source_table;
Example 3: Inserting data from one table to another with a WHERE condition
INSERT INTO target_table (name, email)
SELECT name, email
FROM customers
WHERE city = 'San Francisco';
Example 4: Inserting data from multiple tables to a single table
INSERT INTO target_table (customer_id, product_id, quantity)
SELECT c.customer_id, p.product_id, o.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN products p ON o.product_id = p.product_id;
Explanation of the examples:
Additional notes:
I hope these examples help you understand how to insert data into a table from another table in MariaDB. Please let me know if you have any other questions.
Using a temporary table
- Create a temporary table with the same structure as the target table.
- Insert the data from the source table into the temporary table.
Using a stored procedure
- Create a stored procedure that takes the name of the source table and the target table as parameters.
- Inside the stored procedure, use a SELECT statement to query the data from the source table.
- Use an INSERT statement to insert the data from the SELECT statement into the target table.
Using a view
- Create a view that represents the data you want to insert into the target table.
-- Create a temporary table
CREATE TEMPORARY TABLE tmp_table LIKE target_table;
-- Insert data from the source table into the temporary table
INSERT INTO tmp_table
SELECT *
FROM source_table;
-- Insert data from the temporary table into the target table
INSERT INTO target_table
SELECT *
FROM tmp_table;
-- Drop the temporary table
DROP TEMPORARY TABLE tmp_table;
CREATE PROCEDURE insert_data(source_table VARCHAR(255), target_table VARCHAR(255))
BEGIN
DECLARE done BOOL DEFAULT FALSE;
-- Loop through the rows in the source table
WHILE NOT done DO
SELECT *
FROM source_table
INTO @row
LIMIT 1;
-- Check if there are any more rows to process
IF @row IS NULL THEN
SET done = TRUE;
ELSE
-- Insert the row into the target table
INSERT INTO target_table
VALUES (@row['column1'], @row['column2'], ...);
END IF;
END WHILE;
END PROCEDURE;
-- Call the stored procedure to insert data from the 'source_table' into the 'target_table'
CALL insert_data('source_table', 'target_table');
Example: Using a view
CREATE VIEW v_data AS
SELECT *
FROM source_table;
-- Insert data from the view into the target table
INSERT INTO target_table
SELECT *
FROM v_data;
Which method should I use?
The best method for you will depend on your specific needs. If you only need to insert data once, then using a temporary table or a stored procedure may be a good option. If you need to insert data on a regular basis, then using a view may be a better option.
Additional considerations
- The amount of data you need to insert
- The frequency with which you need to insert data
- The performance of your database
I hope this helps!
sql mariadb