The SQL INSERT INTO statement is one of the most fundamental operations in SQL. It is used to add new rows of data into a database table. Whether you're working with a small application or managing a large database, understanding how to effectively use the INSERT INTO statement is essential for any SQL practitioner.
INSERT INTO Statement?The SQL INSERT INTO statement allows you to insert new records (rows) into an existing table. You can insert one row at a time or multiple rows in a single query, making this statement a versatile and essential part of data management in SQL databases.
The syntax for INSERT INTO varies slightly depending on whether you are inserting specific values into all columns or just a subset of them.
INSERT INTO StatementIf you want to insert values into every column of a table, you need to specify the column names and the corresponding values.
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
table_name: The name of the table where the data will be inserted.column1, column2, column3, ...: The list of columns where you want to insert data.value1, value2, value3, ...: The corresponding values for each column. These must match the order of the columns you’ve specified.If you want to insert data into only some of the columns of the table, you can omit the columns that are not being populated.
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);
In this case, columns not mentioned in the statement will either take their default values or accept NULL (depending on how the table is defined).
INSERT INTO StatementLet's go through some practical examples to illustrate how the INSERT INTO statement works.
Suppose you have a table named employees with the following columns: id, name, position, and salary. To insert a new employee record into this table:
INSERT INTO employees (id, name, position, salary)
VALUES (1, 'John Doe', 'Software Engineer', 75000);
This query inserts a new row with the specified values into all columns of the employees table.
If you only want to insert a name and position without specifying a value for id (assuming id is an auto-increment column) and salary, you can omit those columns from the INSERT INTO statement:
INSERT INTO employees (name, position)
VALUES ('Jane Smith', 'Project Manager');
This will insert the name and position for a new employee, while id and salary will take default values (e.g., id auto-increments and salary may be set to NULL or a default value).
You can insert multiple rows of data in a single query to improve performance and reduce the number of database operations:
INSERT INTO employees (id, name, position, salary)
VALUES
(2, 'Alice Johnson', 'Data Scientist', 80000),
(3, 'Bob Williams', 'Product Manager', 90000),
(4, 'Charlie Brown', 'UX Designer', 70000);
This query inserts three new rows into the employees table in one go.
You can also use the INSERT INTO statement to insert data from another table. This is done by using a SELECT statement to retrieve the data from the source table and insert it into the target table.
INSERT INTO target_table (column1, column2)
SELECT column1, column2
FROM source_table
WHERE condition;
Suppose you want to insert all employees with a salary greater than 80,000 from the employees table into a new high_salary_employees table:
INSERT INTO high_salary_employees (id, name, position, salary)
SELECT id, name, position, salary
FROM employees
WHERE salary > 80000;
This will insert all employees who earn more than 80,000 into the high_salary_employees table.
INSERT INTOWhile the INSERT INTO statement is straightforward, following best practices ensures that your database operations are efficient and error-free.
If you're inserting a large number of rows, it's a good idea to wrap the INSERT INTO statements in a transaction. This ensures that either all the rows are inserted or none of them are (in case of an error).
BEGIN TRANSACTION;
INSERT INTO employees (id, name, position, salary) VALUES (1, 'John Doe', 'Developer', 60000);
INSERT INTO employees (id, name, position, salary) VALUES (2, 'Jane Smith', 'Manager', 80000);
COMMIT;
This way, if one insert fails, the entire transaction can be rolled back, preventing partial inserts.
Before inserting data into a table, ensure that the values you are inserting conform to the column types and constraints. For example:
VARCHAR columns should contain strings.INT columns should contain integers.NOT NULL columns are left empty.When inserting data into a database via an application, it's essential to use prepared statements to prevent SQL injection attacks. Prepared statements ensure that the data is properly sanitized before insertion.
-- Example in MySQL or PostgreSQL with prepared statements
PREPARE stmt FROM 'INSERT INTO employees (name, position, salary) VALUES (?, ?, ?)';
EXECUTE stmt USING @name, @position, @salary;
Prepared statements ensure that user inputs are treated as data, not executable code, making your applications more secure.
INSERT INTO with Auto-Increment ColumnsIf a column is set to auto-increment (e.g., the id field), there is no need to insert a value for that column. Let the database automatically handle it:
INSERT INTO employees (name, position, salary)
VALUES ('David King', 'HR Manager', 75000);
Here, id will be auto-generated by the database.
INSERT INTOHere are some common issues you might encounter while using the INSERT INTO statement:
ERROR: invalid input syntax for type integerINT, you cannot insert a string into it.ERROR: duplicate key value violates unique constraintERROR: null value in column "column_name" violates not-null constraintNULL values are being inserted into columns that are defined with the NOT NULL constraint unless the column is allowed to accept NULL.