Sql server: Insert multiple data into single table using single insert statement - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial

Sql server: Insert multiple data into single table using single insert statement

In this article I am going to explain how to insert multiple data into single table using single insert statement in sql server.


 Implementation:
 We use the Syntax to insert data into database:
Insert into tb(column1,column2..) values(expression1,expression2..)

There are 4 methods to insert multiple rows into table in single statement. First of all create a table.
CREATE TABLE [dbo].[tb_users](
            [Id] [int] IDENTITY(1,1) NOT NULL,
            [Name] [varchar](100) NULL,
            [Password] [varchar](50) NULL,
            [Website] [varchar](100) NULL,
            [emailid] [varchar](50) NULL,
 CONSTRAINT [PK_tb_users] PRIMARY KEY CLUSTERED
(
            [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Method 1:
This is the most traditional method use to insert data into table. Run the below given query:

insert into tb_users(Name,Password,Website,emailid) values('Vijay','12345','http://articlemirror.in','vijay@gmail.com');
insert into tb_users(Name,Password,Website,emailid) values('John','567805','http://john.com','john@gmail.com');
insert into tb_users(Name,Password,Website,emailid) values('David','qwer123','http://david.com','david@gmail.com');
insert into tb_users(Name,Password,Website,emailid) values('Ailsa','12345','http://Ailsa.in','Ailsa@gmail.com');

You can check data in inserted or not by running select query.
select * from tb_users

Result is in front of you. Now truncate the table.
Truncate table tb_users

Method 2: 
insert into tb_users(Name,Password,Website,emailid) values('Vijay','12345','http://articlemirror.in','vijay@gmail.com'),
('John','567805','http://john.com','john@gmail.com'),
('David','qwer123','http://david.com','david@gmail.com'),
(
'Ailsa','12345','http://Ailsa.in','Ailsa@gmail.com')
You can check data in inserted or not by running select query.
select * from tb_users

Result is in front of you. Now truncate the table.
Truncate table tb_users

Method 3: 
insert into tb_users values('Vijay','12345','http://articlemirror.in','vijay@gmail.com');
insert into tb_users values('John','567805','http://john.com','john@gmail.com');
insert into tb_users values('David','qwer123','http://david.com','david@gmail.com');
insert into tb_users values('Ailsa','12345','http://Ailsa.in','Ailsa@gmail.com');

You can check data in inserted or not by running select query.
select * from tb_users

Result is in front of you. Now truncate the table.
Truncate table tb_users

Method 4:
You can also insert using union all.

insert into tb_users(Name,Password,Website,emailid) select 'Vijay','12345','http://articlemirror.in','vijay@gmail.com'
union all
select 'John','567805','http://john.com','john@gmail.com'
union all
select 'David','qwer123','http://david.com','david@gmail.com'
union all
select 'Ailsa','12345','http://Ailsa.in','Ailsa@gmail.com'

You can check data in inserted or not by running select query.
select * from tb_users

Truncate the table.
Truncate table tb_users


What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon