SQL Server INSERT Multiple Rows

SQL Server is a relational Database Management System(RDBMS). It offers various features for creating, and managing databases with its efficient tools. It can handle both small-scale and large-scale industry database applications.

INSERT Statement in SQL Server

The Insert statement is a command of the Data Manipulation Language (DML) of DBMS. After the creation of the table, we use this INSERT statement to insert the rows in the table. In this article let us see the ways to insert the rows into the table.
If we want to insert all the columns of the table in the form of the row with the ‘, ‘ separator we need not explicitly mention all the column names by default all the columns will be considered.

Syntax:

 INSERT INTO tableName
VALUES (row1),
(row2),
(row3);

Explanation: The values in the rows should be comma-separated.

If we want to insert only particular columns of the table in the form of rows ‘ , ‘ separated then we need to explicitly mention the column names of the column with ‘ , ‘ separated that we want to add to the table.

Process to Insert Multiple Rows in the Table

To understand the INSERT MULTIPLE Rows in the Table, we need a table for performing the operation. So here we have coursesActive Table which consists of courseId, courseName, courseCost and, studentsEnrolled Columns. If you don’t know How to Create a Table in SQL Server then Refer to this.

CREATE TABLE CoursesActive
(
courseId INT PRIMARY KEY,
courseName VARCHAR(100),
courseCost INT,
studentsEnrolled BIGINT
);

Let’s Insert some data into the coursesActive Table.

INSERT INTO CoursesActive
VALUES (1001, 'Data Structures And Algorithms', 4050, 20000000),
       (1002, 'Java for Beginners',1215,100000),
       (1003, 'Java Advanced',2435,200000),
       (1004, 'DBMS',1620,3000000),
       (1005, 'Operating Systems',1458, 200000),
       (1006, 'Python', 2000, 1500000),
       (1007, 'Machine Learning',5000,3000000),
       (1008, 'Git and Github',500,1000000),
       (1009, 'C++',1000,30000),
       (1010, 'Data Science',5000, 378678)

Output:

coursesActive Table

Explanation: This query inserts all the rows with the given values, if the column count doesn’t match then it will throw an error or if the column that was getting inserted has the wrong datatype than expected then it will throw an error.

Inserting Multiple Rows with Some Columns Only:

Let’s insert some data into some columns of the table while the remaining columns left in the table place NULL values in it except Primary Key. Because the Primary Key always holds some values in it otherwise it will throw an error.

Query:

INSERT INTO CoursesActive (courseId,courseName)
VALUES (1011, 'DevOps'),
       (1012, 'C#'),
       (1013, '.NET');

SELECT * FROM CoursesActive

Output :

Result

Explanation: This query inserts only the mentiond columns and those mentioned columns are mandatory, other than the mentioned columns remaining will be NULL.

Inserting Multiple Rows Using SELECT and UNION:

With the help of UNION we can also insert data into the table. Let’s check with an example.

Query:

 INSERT INTO CoursesActive 
SELECT 1014, 'DevOps Advanced', 3000, 20000
UNION
SELECT 1015, 'Data Science Advanced', 5000, 2000000

SELECT * FROM CoursesActive

Output :

Result

Explanation: Using the UNION, we are combine the multiple select statements with the rows information and then later the INSERT command inserts the rows into the table. In the result, we can see that 2 rows were added at the end.

Example: Let’s take an example of adding the three rows with only courseId, courseName, studentsEnrolled fields into the CoursesActive Table.

Query:

 INSERT INTO CoursesActive (courseId,courseName, studentsEnrolled)
VALUES (1016, 'DevOps', 100000),
       (1017, 'C#' ,200000),
       (1018, '.NET',230887);

Output:

Explanation: In the explanation we have explicitly mentioned the column names that we want to insert and the three rows are inserted with the columns courseId, courseName, studentsEnrolled other than them the remaining is NULL.

Conclusion

Using the INSERT statement we can insert multiple rows into the table without adding multiple INSERT statements for each row. It is one of the most used and necessary DML commands for starting with the SQL Server. It helps us in adding multiple columns or only the required columns of the table easily.