Insert Statement in MS SQL Server
Last Updated : 16 Aug, 2024
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.
| id | name | age | course |
| 1 | Alice | 20 | Mathematics |
| 2 | Bob | 22 | Physics |
| 3 | Charlie | 21 | Chemistry |
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:
| id | name | age | course |
| 1 | Alice | 20 | Mathematics |
| 2 | Bob | 22 | Physics |
| 3 | Charlie | 21 | Chemistry |
| 4 | David | 23 | Biology |
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:
| id | name | age | course |
| 1 | Alice | 20 | Mathematics |
| 2 | Bob | 22 | Physics |
| 3 | Charlie | 21 | Chemistry |
| 4 | David | 23 | Biology |
| 5 | Eva | NULL | NULL |
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:
| id | name | age | course |
| 1 | Alice | 20 | Mathematics |
| 2 | Bob | 22 | Physics |
| 3 | Charlie | 21 | Chemistry |
| 4 | David | 23 | Biology |
| 5 | Eva | NULL | NULL |
| 12 | Bob | 22 | Physics |
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.
Similar Reads
Insert Into Select statement in MS SQL Server
The INSERT INTO SELECT statement in SQL Server is a versatile feature that enables you to efficiently copy data from one or more tables into another table. This functionality is essential for tasks such as data transfer, backup creation, and data merging. In this article, We will learn to Insert Int
4 min read
SQL Server SELECT INTO Statement
SQL Server is a relational database management system. SQL Server offers robust security features to protect data integrity and confidentiality. It includes authentication, authorization, encryption, and various mechanisms to secure the database environment. It is designed to scale from small applic
6 min read
Select Statement in MS SQL Server
The SELECT statement in SQL Server is a foundational SQL command used for querying and retrieving data from one or more tables within a database. This command allows users to specify which columns and rows to retrieve and apply filters to focus on specific data and perform various operations to mani
4 min read
SQL Server MERGE Statement
MERGE is a powerful SQL statement that allows you to perform INSERT/UPDATE/DELETE operations depending on the conditions you specify. It makes it easy to synchronize data between source and destination tables. This makes it efficient to handle data changes in one single statement. MERGE makes it eas
6 min read
SQL Server MERGE Statement
SQL Server MERGE statement combines INSERT, UPDATE, and DELETE operations into a single transaction. MERGE in SQL ServerThe MERGE statement in SQL provides a convenient way to perform INSERT, UPDATE, and DELETE operations together, which helps handle the large running databases. But unlike INSERT, U
2 min read
SQL INSERT INTO SELECT Statement
In SQL, the INSERT INTO statement is used to add or insert records into the specified table. We use this statement to insert data directly into a table by specifying column names in a specific order. The SELECT statement is used to retrieve data from the table, and it can be used in conjunction with
6 min read
SQL | INSERT IGNORE Statement
We know that a primary key of a table cannot be duplicated. For instance, the roll number of a student in the student table must always be distinct. Similarly, the EmployeeID is expected to be unique in an employee table. When we try to insert a tuple into a table where the primary key is repeated,
2 min read
What is Nested Select Statement in SQL Server
SQL Server is a powerful relational database management system. Sql Server is very good with its robustness and scalability. SQL Server operates as a client-server structure, imparting centralized management and scalability for huge-scale applications and enterprise-stage solutions. It offers advanc
3 min read
PL/SQL INSERT Statement
The PL/SQL INSERT statement is vital for adding new records to a database table. By specifying the table's name and providing values for its columns, users can populate their database with essential information. This functionality enables efficient data entry and ensures the completeness of datasets
3 min read
SQL SERVER | Conditional Statements
While loop: In SQL SERVER, while loop can be used in similar manner as any other programming language. A while loop will check the condition first and then execute the block of SQL Statements within it as long as the condition evaluates true. Syntax: WHILE condition BEGIN {...statements...} END; Par
2 min read