In Microsoft SQL Server (MS SQL), the INSERT statement is used to add new rows to a table. Here’s a detailed explanation of how to use the INSERT statement in MS SQL:

Basic Syntax

Example

Assume you have a table named employees with columns id, name, and position. To insert a new employee into this table, use the following query:

Inserting Multiple Rows

You can insert multiple rows in a single INSERT statement by separating each set of values with a comma:

Inserting Default Values

If a column has a default value specified and you want to use that default, you can omit the column from the INSERT statement:

Alternatively, you can use the DEFAULT keyword if you want to explicitly state that a column should use its default value:

Inserting Data from Another Table

You can insert data into a table from another table using a SELECT statement. For example, if you have an old_employees table and want to insert rows into new_employees where the start_date is after a certain date, you could use:

Inserting Data with Identity Columns

If your table has an IDENTITY column (an auto-incrementing column), you don’t need to insert a value into this column; SQL Server will automatically generate it. For example:

Inserting data without specifying the id column:

Using OUTPUT Clause

SQL Server allows you to use the OUTPUT clause to return information about the rows affected by the INSERT statement. For example:

This will return the id and name of the newly inserted row.

Handling Errors

To handle potential errors during an INSERT, you might use TRY...CATCH blocks:

This allows you to capture and handle errors that might occur during the insert operation.