How to Insert Data in SQL
In SQL, the INSERT statement is used to insert new rows of data into a table. You can insert one or multiple rows at once, and you can specify the values for each column in the table. Below are the basic syntaxes and examples to help you understand how to insert data in SQL.
1. Basic Syntax for Inserting a Single Row
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
- table_name is the name of the table where you want to insert the data.
- column1, column2, column3, ... are the column names in the table.
- value1, value2, value3, ... are the corresponding values that you want to insert into those columns.
Example:
INSERT INTO employees (first_name, last_name, age, department) VALUES ('John', 'Doe', 29, 'HR');
This query inserts a new employee, John Doe, aged 29, into the employees table with the department set to 'HR'.
2. Inserting Multiple Rows at Once
You can insert multiple rows of data in a single INSERT statement by separating each set of values with commas.
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...), (value4, value5, value6, ...), (value7, value8, value9, ...);
Example:
INSERT INTO employees (first_name, last_name, age, department) VALUES ('Jane', 'Smith', 34, 'Finance'), ('Alice', 'Johnson', 28, 'IT'), ('Bob', 'Brown', 40, 'Marketing');
This query inserts three new employee records in the employees table.
3. Inserting Data Without Specifying Column Names
If you are inserting values into all columns in the table, you can omit the column names. However, you must provide values for all columns in the exact order they appear in the table.
INSERT INTO table_name VALUES (value1, value2, value3, ...);
Example:
INSERT INTO employees VALUES ('Mark', 'Taylor', 32, 'Sales');
This query inserts a new employee with the specified details.
4. Using Default Values
If a column in the table is set to accept default values or can be NULL, you can either omit that column in your INSERT statement or explicitly insert the DEFAULT keyword for that column.
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, DEFAULT, value3, ...);
Example:
INSERT INTO employees (first_name, last_name, department) VALUES ('Sam', 'Wilson', 'HR');
This query inserts a new employee where the age column is filled with the default value defined in the table schema.
5. Inserting Data from Another Table (Using SELECT)
You can also insert data into a table by selecting data from another table. This is particularly useful when you want to copy records or parts of records from one table to another.
INSERT INTO table_name (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM another_table WHERE condition;
Example:
INSERT INTO employees (first_name, last_name, age, department) SELECT first_name, last_name, age, department FROM temp_employees WHERE start_date > '2024-01-01';
This query inserts employees from the temp_employees table into the employees table, but only those who started after January 1, 2024.
Key Notes
- Data Types: Make sure the values you are inserting match the data types of the respective columns.
- Auto-Increment Columns: If a column is set to auto-increment (e.g., primary key), you do not need to insert a value for it. It will automatically be generated.
- Transaction Handling: In some cases, you may want to use transactions (BEGIN TRANSACTION, COMMIT, ROLLBACK) to ensure data integrity.
Conclusion
The INSERT statement is fundamental for adding data to your database and should be used with care, especially when dealing with multiple rows or large datasets. By mastering the INSERT command and understanding its variations, you can efficiently manage data in your relational databases.