How To Insert Multiple Rows in SQL With Example

How To Insert Multiple Rows in SQL With Example Tutorial From Scratch

How To Insert Multiple Rows in SQL With Example is today’s topic. In SQL, inserting values in the rows is a tedious task when a table comes with a lot of attributes. Previously, we were habituated to add values in row one at a time which is very time-taking and which also results in a lot of error. If we want to get rid of this, we are going to learn how to insert the multiple rows in a table at a time.

How To Insert Multiple Rows in SQL With Example

There are different methods to insert values in a table:

1. Using INSERT statement.
2. Using INSERT INTO SELECT statement.
3. Using UNION ALL keyword.

#Syntax

INSERT INTO table_name(column1, column2, ……., column n) VALUES (values in
row 1), (values in row 2), ……… (values in row n);

#Parameters

1) Table_name is the name of the table.
2) Columns are the name of the columns.
3) Values in rows are the set of the values to be inserted in the table.

#Note
1) At a time, you can insert 1000 rows using INSERT statements. To insert more rows than that used multiple insert keywords or use BULK INSERT.
2) Only SQL SERVER 2008 allows adding multiple rows in the table.

#Examples

Suppose a table is created named as STUDENT.

ID NAME CITY
101 Shubh Kolkata
102 Sandeep Asansol
103 Shouvik Patna

#QUERY

INSERT INTO STUDENT (ID, NAME, CITY) 
VALUES (101,’Shubh’,’Kolkata’), 
       (102,’Sandeep’,’Asansol’), 
       (103,’Shouvik’,’Patna’);

See the output.

How To Insert Multiple Rows in SQL With Example

Using INSERT INTO SELECT statement.

#Syntax

INSERT INTO STUDENT (ID, name, city) SELECT * from new_student;

See the following output.

How To Insert Multiple Rows in SQL

#Explanation

In the parentheses of table student, we have given the column name in the same order which was discussed in the table in the previous example. Now when we have executed a select * statement, then the values from new_student table gets copied to student table.

The order should be the same as that of the student table if you are affecting the whole table.

#NOTE

It is not necessary that all rows should be executed, you can also insert specific rows using where condition and also specific columns which you want.

#Using UNION ALL keyword

#Syntax

Insert into table (column1, column2, …., column n) SELECT values UNION ALL SELECT values_1 UNION ALL ….;

#Parameters

  1. The table is the name of the table.
  2. Columns are the name of the columns.
  3. Values are the set of data you want to insert.
  4. UNION ALL is the keyword.

#Example

Consider table STUDENT having attributes like ID, NAME.

Now to add values use the following query.

#Query

Insert into student (ID, Name) Select 1,'Shubh' UNION ALL Select 2,'Sandeep' UNION ALL Select 3,'Shouvik';

#Output

How To Insert Multiple Rows in SQL With Example Tutorial

#Explanation

Here values are inserted in the same order as mentioned in parentheses. UNION ALL is used for merging all the values in rows.

Finally, How To Insert Multiple Rows in SQL With Example is over.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *