SQL Server Data Manipulation: INSERT Statement Tutorial

Understanding the INSERT Statement

The INSERT statement adds new rows to a table in SQL Server.

Its basic syntax requires specifying the target table and the values to insert.

You can insert data by listing values directly or by selecting data from another table.

• INSERT INTO table_name (column1, column2) VALUES (value1, value2);
• INSERT INTO table_name VALUES (value1, value2, value3);
• INSERT INTO table_name (column1, column2) SELECT columnA, columnB FROM other_table;

Inserting Single Rows

To insert a single row, specify the table name, columns, and corresponding values.

Specifying columns is recommended to avoid errors if the table schema changes.

• Example syntax: INSERT INTO Employees (FirstName, LastName, Age) VALUES ('John', 'Doe', 30);

Inserting Multiple Rows

SQL Server supports inserting multiple rows in a single statement using multiple VALUES clauses.

This improves performance by reducing the number of separate insert commands.

• Example syntax: INSERT INTO Employees (FirstName, LastName, Age) VALUES ('Alice', 'Smith', 28), ('Bob', 'Brown', 35);

Inserting Data from Another Table

You can insert data into a table by selecting rows from another table.

This is useful for copying or transforming data within the database.

• Example syntax: INSERT INTO EmployeesArchive (FirstName, LastName, Age) SELECT FirstName, LastName, Age FROM Employees WHERE Age > 60;

Best Practices for Using INSERT

Following best practices ensures your INSERT statements are reliable and maintainable.

• Always specify column names in the INSERT statement.
• Use transactions when inserting multiple related rows to maintain data integrity.
• Validate data types and constraints before inserting.
• Avoid inserting NULL values unless the column allows it.
• Use parameterized queries in application code to prevent SQL injection.

Common Mistakes to Avoid

Being aware of common pitfalls helps prevent errors during data insertion.

• Mismatching the number of columns and values.
• Inserting data into the wrong columns due to missing column list.
• Ignoring constraints like NOT NULL or UNIQUE, causing insert failures.
• Not handling identity columns properly (e.g., trying to insert explicit values).
• Forgetting to commit transactions after inserts.