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


Post a Comment

[blogger]

MKRdezign

Contact Form

Name

Email *

Message *

Powered by Blogger.
Javascript DisablePlease Enable Javascript To See All Widget