The INSERT INTO Command
tags: #sql/basic_statements
When inserting data into a table that has a foreign key (FK) column, you need to supply the corresponding referenced primary key (PK) value for the FK. The FK column will not populate on its own, but rather it needs to be explicitly specified during the insertion of new records. This ensures that the referential integrity constraint is maintained, which means that each value in the FK column must match a valid value in the referenced PK column.
What is the INSERT INTO command?
The INSERT INTO statement is used to insert new records in a table.
How does this work?
Attribute values must be listed in the same order as the attributes specified in the CREATE TABLE command.
Note: any integrity constraints are enforced automatically.
Syntax
It is possible to write the INSERT INTO statement in two ways:
- Specify both the column names and the values to be inserted (used if you want to enter values to specific columns):
INSERT INTO table_name (col1, col2, col3,...)
-- first row: values for the columns in the list above
VALUES (value1, value2, value3)
-- first row: values for the columns in the list above
VALUES (value1, value2, value3)
-- add more rows as needed
...
- If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, you need to make sure that the order of the values are in the same order as the columns in the table:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
Bulk Loading of Tables
Alternatively, we can select data from one or more tables and insert it into another table:
INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;
Alternative method using LIKE and WITH DATA:
CREATE TABLE new_Table LIKE existing_table
(SELECT * FROM column1 WHERE column1=value)
WITH DATA;
- This creates a new table that has the same schema (columns, data types, constraints, etc.) as the existing table, but only includes the tuples from the given column of the existing table where the given column has the following specified value.
Clauses combined with INSERT INTO
1. OR IGNORE Clause
INSERT INTO OR IGNORE will attempt to insert the new row into the table, and if a constraint violation occurs, it will simply ignore the row and move on to the next one.
This ensures that if a particular data is already in the table, there are no duplicate rows inserted:
INSERT INTO OR IGNORE <tablename> (column1,...)
VALUES (value1,...)
2. OR REPLACE Clause
INSERT INTO OR REPLACE will attempt to insert the new row into the table.
If a constraint violation occurs, it will delete the existing row that is causing the conflict, and replace it with the new row.
The idea of the REPLACE statement is that when a UNIQUE or PRIMARY KEY constraint is violated, it does one of the following:
INSERT OR REPLACE INTO table_name(column(s))
VALUES(value(s));