INSERT into a table records extracted from another table in SQL and MS Access

SQL (Structured Query Language)

SQL is a standard language used to communicate with databases. To insert records from one table to another, we use the INSERT INTO statement along with a SELECT subquery.

Basic syntax:

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

Explanation:

• target_table: The name of the table where you want to insert the records.
• column1, column2, ...: The columns in the target table that you want to populate.
• SELECT column1, column2, ...: Selects the columns from the source table that correspond to the columns in the target table.
• FROM source_table: Specifies the name of the source table from which you want to extract the records.
• WHERE condition: (Optional) Specifies a condition that filters the records to be inserted.

Example:

Let's assume we have two tables: "Customers" and "Orders". We want to insert all orders from customers who live in "New York" into a new table called "NewYorkOrders".

INSERT INTO NewYorkOrders (OrderID, CustomerID, OrderDate)
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE City = 'New York');

MS Access

In MS Access, you can use the same SQL syntax as above. However, you can also use the query builder to create and edit queries visually.

Steps:

1. Create a new query: Go to the "Create" tab and select "Query Design".
2. Add tables: Double-click the "Customers" and "Orders" tables in the "Show Tables" dialog.
3. Join tables: If necessary, join the tables based on the common column (e.g., "CustomerID").
4. Add fields: Drag and drop the desired fields from the "Orders" table into the query grid.
5. Add criteria: In the criteria row, enter the condition for filtering the records (e.g., "City = 'New York'").
6. Run the query: Click the "Run" button to execute the query and insert the records into the new table.

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

INSERT INTO NewYorkOrders (OrderID, CustomerID, OrderDate)
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE City = 'New York');

• target_table: 目的のテーブル名
• column1, column2, ...: 目的のテーブルの列名
• SELECT column1, column2, ...: ソーステーブルから選択する列
• FROM source_table: ソーステーブル名
• WHERE condition: 条件

1. 新しいクエリを作成
2. テーブルを追加
3. フィールドを追加
4. 条件を追加
5. クエリを実行

Insert Records Between Tables

SQL

INSERT INTO target_table (column1, column2, ...)
VALUES (value1, value2, ...);

INSERT INTO Customers (CustomerID, CustomerName, City)
VALUES (1001, 'John Doe', 'New York');

• VALUES (value1, value2, ...): 挿入する値

1. 新しいレコードを追加
2. フィールドに値を入力
3. レコードを保存

Note:

• INSERT INTO ... SELECT ... FROM ... は、複数のレコードを一度に挿入する際に使用します。
• INSERT INTO ... VALUES ... は、単一のレコードを挿入する際に使用します。
• MS Access では、クエリのビルダーを使用してこれらの操作を視覚的に行うこともできます。

Alternative Methods

Using a Temporary Table:

• Create a temporary table to store the intermediate results from the SELECT query.
• Insert records into the temporary table.
• Insert records from the temporary table into the target table.

CREATE TEMPORARY TABLE TempTable (column1, column2, ...);
INSERT INTO TempTable (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM TempTable;

Using a Common Table Expression (CTE):

• Define a CTE to represent the results of the SELECT query.

WITH CTE AS (
    SELECT column1, column2, ...
    FROM source_table
    WHERE condition
)
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM CTE;

Using a Stored Procedure:

• Create a stored procedure to encapsulate the logic for inserting records.
• Call the stored procedure to insert records.

CREATE PROCEDURE InsertRecords (
    @column1,
    @column2,
    ...
)
AS
BEGIN
    INSERT INTO target_table (column1, column2, ...)
    VALUES (@column1, @column2, ...);
END;

Using a Trigger:

• Create a trigger on the source table to automatically insert records into the target table when a specific event occurs (e.g., INSERT, UPDATE, DELETE).

CREATE TRIGGER TriggerName
ON source_table
AFTER INSERT
AS
BEGIN
    INSERT INTO target_table (column1, column2, ...)
    SELECT column1, column2, ...
    FROM inserted;
END;