Insert Statement in MS SQL Server

The SQL Server INSERT statement is a fundamental command used to add new rows of data to a table. Whether we are inserting specific values, utilizing default values or copying data from another table.

In this guide, we’ll explore various ways to use the Insert statement in MS SQL Server with the help of various examples and so on.

SQL Server INSERT Statement

• The SQL Server INSERT statement is used to add new rows of data into a table.
  
• It allows us to insert specific values into defined columns or even copy data from another table.

Description

The INSERT statement is a DML (Data Manipulation Language) command used to insert data into a table in SQL Server. You can use this statement to add a single row, multiple rows, or data from another query result.

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, column3, ...: The columns in the table where you want to insert data.
• value1, value2, value3, ...: The corresponding values for each column.

Alternatively, you can use the INSERT INTO ... SELECT syntax to insert data from another table.

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

Parameters or Arguments

• table_name: The table into which data will be inserted.
• column1, column2, column3, ...: The specific columns in the table that will receive the new data. If no columns are specified, values must be provided for all columns in the table.
• value1, value2, value3, ...: The values to be inserted into the specified columns.
• another_table: The table from which to select and insert data when using the INSERT INTO ... SELECT syntax.
• condition: The condition to filter rows from the source table when using the INSERT INTO ... SELECT syntax.

Examples of Insert statement in SQL Server

We will consider table called student as sown below to perform various examples and so on.

Example 1: Using VALUES Keyword

The VALUES keyword is commonly used to insert a single row or multiple rows into a table.

INSERT INTO students (id, name, age, course)
VALUES (4, 'David', 23, 'Biology');

Output:

Explanation: A new row with the values for id, name, age, and course has been inserted into the students table.

Example 2: Using DEFAULT VALUES Keyword

The DEFAULT VALUES keyword is used when we want to insert a new row with default values specified in the table's schema or null if no default is provided.

INSERT INTO students (id, name)
VALUES (5, 'Eva');

Output:

Explanation: A new row with the specified id and name values is inserted into the students table, while age and course are set to their default values (NULL in this case).

Example 3: Using SELECT Statement

We an use a SELECT statement to insert data from another table or based on a query.

INSERT INTO students (id, name, age, course)
SELECT id + 10, name, age, course
FROM students
WHERE course = 'Physics';

Output:

Explanation: A new row is inserted into the students table with values derived from a SELECT query. The id is incremented by 10 for differentiation, and only the row where course = 'Physics' is inserted.

Conclusion

The INSERT statement in SQL Server provides flexibility in how you add data to your tables, whether it's inserting specific values, using default values, or copying data from other tables. By understanding and utilizing these different forms of the INSERT statement, you can effectively manage data within your SQL Server databases, ensuring accuracy and consistency.