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:
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 :
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 :
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.
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.
Contact Us