AppDividend
Latest Code Tutorials

How To Insert Multiple Rows in SQL With Example

0

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 table_1 (column1, column2, …., column n) 
SELECT column1, column2, …., column n from table_2 ;

#Parameters

  • table_1 is the name of the newly created table
  • table_2 is the name of the previously created table.
  • Columns are the respective columns in which you want to insert values.

#Examples

Consider table STUDENT having attributes like ID, NAME, CITY, as discussed in the previous example. Now to add values to it, we are going to use the table.

new_student
ID First_name City
1 Anshuman Asansol
2 Karthik Allahabad
3 Karan Malda

 

#Query

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.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.