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.

In previous article I have explained how to create chart using Highchart in MVC, howto create chart with database data using MVC and how to upload and downloadFile using MVC.

 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://www.aspmantra.com','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://www.aspmantra.com','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://www.aspmantra.com','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://www.aspmantra.com','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