MSDN Documentation

INSERT Statements

The INSERT statement is used to add new rows of data to a table in a database. It's a fundamental operation for populating your tables and keeping your data up-to-date.

Syntax

There are two primary syntaxes for the INSERT statement:

1. Inserting a Single Row with Specific Values

This syntax allows you to specify the values for each column in the new row.

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

• table_name: The name of the table where you want to insert data.
• column1, column2, ...: The names of the columns you are providing values for. If you omit this list, you must provide a value for every column in the table, in the order they are defined.
• value1, value2, ...: The values to be inserted into the corresponding columns. The data types of these values must match the data types of the columns.

Example:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
VALUES (101, 'Alice', 'Smith', 'Engineering');

2. Inserting a Single Row Using Values from Another Table (Subquery)

This syntax is useful when you want to copy data from one table to another or insert data based on the results of a query.

INSERT INTO table_name (column1, column2, column3, ...)
SELECT columnA, columnB, columnC, ...
FROM another_table
WHERE condition;

• The number and order of columns in the INSERT INTO clause must match the number and order of columns in the SELECT statement.
• The data types of the selected columns must be compatible with the target columns.

Example:

INSERT INTO HighSalaryEmployees (EmployeeID, FullName)
SELECT EmployeeID, FirstName + ' ' + LastName
FROM Employees
WHERE Salary > 80000;

Inserting Multiple Rows

Most SQL dialects allow inserting multiple rows in a single statement by providing comma-separated value lists.

INSERT INTO Products (ProductID, ProductName, Price)
VALUES
    (1, 'Laptop', 1200.00),
    (2, 'Keyboard', 75.50),
    (3, 'Mouse', 25.99);

Important Considerations

• Data Types: Ensure that the data types of the values you are inserting are compatible with the column's data type.
• Constraints: Be aware of any constraints (e.g., PRIMARY KEY, UNIQUE, NOT NULL, FOREIGN KEY, CHECK) defined on the table. Violating a constraint will result in an error.
• Default Values: If a column has a default value and you don't provide a value for it, the default value will be used.
• Auto-Increment/Identity Columns: For columns that automatically generate unique identifiers (like IDENTITY in SQL Server or AUTO_INCREMENT in MySQL), you typically do not provide a value. The database handles this automatically.

Example with Default Value and Identity Column:

-- Assuming OrderID is an IDENTITY column and OrderDate has a DEFAULT value of GETDATE()
INSERT INTO Orders (CustomerID, ProductID)
VALUES (50, 12);