SQL is an essential component in managing massive amounts of data in modern-day applications. When dealing with two tables in a database, you might need to copy data from both tables to one central table for analysis or data processing. This task can be achieved using a SQL statement – the “INSERT INTO” statement. In this article, we'll explore how to copy data from two SQL tables to one central table using “INSERT INTO” statements, including some code examples.
Overview of “INSERT INTO” Statement
The “INSERT INTO” statement is a SQL statement used to insert or add a new record to an existing database table. It also allows the transfer or copy of data from one table to another table. This statement is useful for copying data from two tables and inserting that data into a new table.
This statement is typically used as follows:
- Specify the target table in which data is to be inserted
- List the columns into which data is to be inserted
- Provide values for the columns, either explicitly or by referencing data from another table
“INSERT INTO” statement in SQL
The “INSERT INTO” statement in SQL is used to add data to an existing database table. Additionally, it can also copy data from one table and insert into a new table. The syntax of the “INSERT INTO” statement is:
INSERT INTO table_name (column_name) SELECT column_name FROM table1_name JOIN table2_name ON table1_column=table2_column;
or
INSERT INTO table_name (column_name) VALUES (value1), (value2), (value3), ...;
Syntax explanation:
- “table_name” is the name of the table where the data will be inserted
- “column_name” is the name of the column in which the data is to be inserted
- “value1, value2, value3, …” are the values of the respective columns
When copying data from two tables to one central table, the “SELECT” statement selects the columns from the combined table, which is the result of the “JOIN” condition. The “JOIN” condition specifies the relationship between the two tables, while the “ON” clause defines the column(s) on which the tables will be joined.
Code Examples
Let’s consider an example where we have two tables, pets_info, and pets_care, and we want to copy data from both tables to a central table pets_central. The pets_info table contains the basic information of each pet, and pets_care table contains the health and care information of each pet.
Example 1: Insert Into Statement with Concatenation
The following code example demonstrates how to copy data from two tables to a new table using the “INSERT INTO” statement with concatenation:
INSERT INTO pets_central (pet_id, pet_name, pet_breed, pet_vaccination, pet_grooming)
SELECT
CONCAT(pt.pet_name, '-', LEFT(pt.pet_breed, 3)) AS pet_id,
pt.pet_name AS pet_name,
pt.pet_breed AS pet_breed,
pc.pet_vaccination AS pet_vaccination,
pc.pet_grooming AS pet_grooming
FROM
pets_info AS pt
JOIN
pets_care AS pc
ON
pt.pet_id = pc.pet_id;
Explanation: The code above selects the columns to be inserted into the pets_central table from the pets_info and pets_care tables. Here, we use CONCAT to combine the pet name with the first three letters of its breed to create a pet_id column. The pet_vaccination and pet_grooming columns are selected from the pets_care table. The joined tables are specified in the SELECT statement, and the selected columns are inserted into the pets_central table.
Example 2: Insert Into Statement with Hardcoded Values
The following code example demonstrates how to use the “INSERT INTO” statement with hardcoded values to copy data from two tables to a new table:
INSERT INTO pets_central (pet_id, pet_name, pet_breed, pet_vaccination, pet_grooming)
VALUES
('pet_01', 'Max', 'German Shepherd', 'Yes', 'Monthly'),
('pet_02', 'Bella', 'Beagle', 'Yes', 'Weekly'),
('pet_03', 'Luna', 'Golden Retriever', 'Yes', 'Monthly'),
('pet_04', 'Charlie', 'Chihuahua', 'No', '-');
Explanation: The code above inserts hardcoded data into the pets_central table. Here, we are inserting data for the pet_id, pet_name, pet_breed, pet_vaccination, and pet_grooming columns.
Conclusion
Copying data from multiple tables into one central table is a common task in an SQL database. The “INSERT INTO” statement is the key to achieving this task. You can copy data from different tables into one central table using the SQL statements. In this article, we covered how to use the “INSERT INTO” statement with code examples. The examples demonstrated how to copy data from two tables to a new table using concatenation and hardcoded values. To summarize, the “INSERT INTO” statement is an essential SQL statement that allows the transfer or copy of data from one table to another in a database.
here's some additional information on the previous topics covered:
SQL Subqueries
An SQL subquery is a query that is nested inside another query. It is a powerful feature of SQL that allows a query result to be used as an input to another query. There are two main types of subqueries in SQL: correlated and non-correlated subqueries.
A non-correlated subquery can be run independently of the outer query and returns a single value to be used in the outer query.
Example:
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);
A correlated subquery is a subquery that depends on the outer query. The inner query references a column from the table of the outer query. It is executed for each row in the outer query result, and it depends on the current row being processed.
Example:
SELECT id, name, (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.id)
AS num_orders FROM customers;
SQL Transactions
A transaction is a sequence of SQL statements that are treated as a single unit of work. Transactions are used to ensure that a series of SQL statements is executed or rolled back completely and consistently. Transactions ensure that either all SQL statements are executed or none are executed at all. Transactions consist of the following properties, usually referred to as the ACID properties:
- Atomicity: All SQL statements in a transaction are treated as a single unit of work.
- Consistency: A transaction brings the database from one valid state to another.
- Isolation: Transactions are executed independently of each other.
- Durability: Once a transaction is committed, its changes remain in the database even in case of failures.
Example:
START TRANSACTION;
INSERT INTO orders (product_id, customer_id, order_date) VALUES (1, 1, '2022-01-01');
UPDATE customers SET balance = balance - 100 WHERE id = 1;
COMMIT;
This transaction will insert a new row into the "orders" table, update the balance of the customer in the "customers" table, and commit the changes to the database. If any of the statements in the transaction fail, the transaction will be rolled back, and all changes made to the database will be undone.
SQL User Defined Functions
A user-defined function (UDF) in SQL is a function that is created and managed by the user. It is a subroutine that can be called from within an SQL statement, like any other function. UDFs are useful when you want to perform specific calculations or transformations on your data that are not natively provided by SQL.
Example:
CREATE FUNCTION get_discount (total_amount INT)
RETURNS INT
BEGIN
DECLARE discount INT;
IF total_amount > 1000 THEN
SET discount = 100;
ELSEIF total_amount > 500 THEN
SET discount = 50;
ELSE
SET discount = 0;
END IF;
RETURN discount;
END;
In this example, a UDF named "get_discount" is created, which takes an integer argument and returns an integer. The function calculates the discount based on the total amount and returns it.
UDFs can be used in SQL statements like any other function. For example:
SELECT id, name, get_discount(total_amount) AS discount FROM orders;
This statement will select the "id", "name", and "discount" columns from the "orders" table, where the "discount" is calculated using the "get_discount" function.
Popular questions
-
What is the purpose of the INSERT INTO statement in SQL?
Answer: The INSERT INTO statement is used in SQL to insert or add a new record to an existing database table. It can also be used to copy data from one table to another table. -
How do you copy data from two tables to one central table in SQL?
Answer: To copy data from two tables to one central table in SQL, you can use the INSERT INTO statement with a SELECT statement that combines data from the two tables using a JOIN statement. -
What is the difference between a non-correlated subquery and a correlated subquery in SQL?
Answer: A non-correlated subquery in SQL can be run independently of the outer query and returns a single value to be used in the outer query. A correlated subquery depends on the outer query, and it is executed for each row in the outer query result. -
What are the ACID properties of a transaction in SQL?
Answer: The ACID properties of a transaction in SQL are Atomicity, Consistency, Isolation, and Durability. Atomicity ensures that all SQL statements in a transaction are treated as a single unit of work. Consistency ensures that a transaction brings the database from one valid state to another. Isolation ensures that transactions are executed independently of each other. Durability ensures that once a transaction is committed, its changes remain in the database even in case of failures. -
What is a user-defined function (UDF) in SQL?
Answer: A user-defined function (UDF) in SQL is a function that is created and managed by the user. It is a subroutine that can be called from within an SQL statement, like any other function. UDFs are useful when you want to perform specific calculations or transformations on your data that are not natively provided by SQL.
Tag
Joining.
Example:
SELECT *
FROM table1
INNER JOIN table2
ON table1.id = table2.id;
