The INSERT statement is used to insert new records into a table.
Syntax
INSERT INTO table_name [(column1, column2, ...)]
VALUES (value1, value2, ...) [ , (value1, value2, ...) [, ...] ];
Parameters:
table_name: Name of the table where you want to insert records.column1, column2, ...: Optional list of column names into which you want to insert data. If you omit this, you must provide values for all columns in the table in the correct order.value1, value2, ...: Values that correspond to the columns that you are inserting data into. It is possible to provide sets of values to be inserted within one query.query: A SELECT query that provides the data for the insertion.
Default and NULL values
When you execute an INSERT statement, it’s not necessary to provide a value for every column in the table. You can omit one or more columns from the column list. If you do so, Polypheny will use the default value defined for each omitted column.
Here’s an example:
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
salary INT DEFAULT 50000
);
In this table, the salary column has a default value of 50000. So, if you execute the following statement:
INSERT INTO employees (id, first_name, last_name)
VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith');
Both John and Jane will be inserted into the table with a salary of 50000, which is the default value.
If no default value is defined for the omitted columns, and those columns allow NULL values, Polypheny will insert NULL. If the column does not allow NULLs and no default value has been set, an error will occur.
Inserting Data from Another Table (INSERT…SELECT)
You can insert data from one table into another using an INSERT...SELECT statement. This is useful when you want to copy data from one table to another, or when you want to summarize data from one table and insert it into another.
The INSERT...SELECT statement combines the INSERT and SELECT statements. The SELECT statement retrieves data from one or more tables, and then the INSERT statement inserts that data into the specified table.
The syntax is as follows:
INSERT INTO target_table [(column1, column2, ...)]
SELECT column1, column2, ...
FROM source_table
WHERE condition;
For example, to insert employees with a salary higher than 50000 from the employees table into the high_salary_employees table, you would use:
INSERT INTO high_salary_employees (id, first_name, last_name)
SELECT id, first_name, last_name
FROM employees
WHERE salary > 50000;
This would copy all employees from the employees table with a salary higher than 50000 to the high_salary_employees table.
Example
The following SQL statement inserts a new record into the employees table:
INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 50000);
This statement inserts a new employee with the first name ‘John’, last name ‘Doe’, and a salary of 50000.
The following SQL statement inserts multiple new records into the employees table:
INSERT INTO employees (first_name, last_name, salary)
VALUES
('John', 'Doe', 50000),
('Jane', 'Smith', 60000),
('Bob', 'Johnson', 70000);
This statement inserts new employees with the provided names and salaries.
If you don’t provide the column names, you should provide values for all columns, in the order they were defined in the table. Here’s an example:
INSERT INTO employees
VALUES
(1, 'John', 'Doe', 50000),
(2, 'Jane', 'Smith', 60000),
(3, 'Bob', 'Johnson', 70000);
In this example, the first value of each set is the id of the employee.
You can also insert data into a table based on a query. Here’s an example:
INSERT INTO high_paid_employees (first_name, last_name, salary)
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 70000;
In this example, a new record is inserted into the high_paid_employees table for each employee in the employees table who has a salary greater than 70000.
