How do you insert data into a table in SQL?

To insert data into a table in SQL, we use the INSERT INTO statement. It allows us to add new rows to a table, either by specifying the values directly or by inserting results from another query.

Basic Syntax: #

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

For example, suppose we have an employees table with columns employee_id, name, department, salary. To insert a new employee:

INSERT INTO employees (employee_id, name, department, salary)
VALUES (4, 'David', 'Finance', 55000);

This adds a single row to the employees table.

Inserting Multiple Rows: #

You can insert multiple rows in a single statement:

INSERT INTO employees (employee_id, name, department, salary)
VALUES 
(5, 'Eva', 'HR', 50000),
(6, 'Frank', 'IT', 60000);

Inserting Data from Another Table: #

We can also insert data from the result of a SELECT query:

INSERT INTO employees_archive (employee_id, name, department, salary)
SELECT employee_id, name, department, salary
FROM employees
WHERE department = 'IT';

This is useful for archiving or moving data between tables.

In a real-world scenario, I used INSERT to populate a staging table from a CSV import. Initially, I loaded all rows into a temporary table and then inserted validated records into the main table using INSERT INTO ... SELECT.

Challenges I’ve faced include:

• Ensuring data integrity, especially when the table has primary keys, foreign keys, or NOT NULL constraints. Missing or duplicate values can cause errors.
• Handling bulk inserts efficiently — inserting millions of rows one by one is slow. I used batch inserts or bulk load utilities (like BULK INSERT in SQL Server or LOAD DATA INFILE in MySQL) to improve performance.

Limitations:

• INSERT only adds new data — it does not update existing rows. For combined insert/update scenarios, MERGE or UPSERT is needed.
• Overusing INSERT without indexing or constraints may lead to duplicate or inconsistent data.

In summary, INSERT INTO is the standard way to add data to tables. Careful handling of constraints, batch inserts, and data validation ensures that data is added accurately and efficiently.