INSERT statement is used to insert new records into a table.
INSERT INTO table_name [(column1, column2, ...)] VALUES (value1, value2, ...) [ , (value1, value2, ...) [, ...] ];
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.
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.
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.
INSERT...SELECT statement combines the
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
The following SQL statement inserts a new record into the
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
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.