Multiple Insertion into a table [Resolved]

Posted by Muhsinathk under Sql Server on 6/7/2012 | Points: 10 | Views : 1512 | Status : [Member] | Replies : 28
Can we insert multiple rows at a time in to a table?
Is there any solution?




Responses

Posted by: CGN007 on: 6/7/2012 [Member] Silver | Points: 50

Up
0
Down

Resolved
Yes we can,Please see the following code...,

Syntax
INSERT INTO Table_Name VALUES ( Value1, Value2 ), ( Value1, Value2 )

insert into  EmployeeMas values('Raj','aaa',5000,'ERP'),('Ram','bbb',4000,'ERP'),('Sachin','ccc',5000,'MED'),('Rahul','ddd',8000,'MED')


Table creation Script

create table EmployeeMas


(Firstname varchar(10),

Lastname varchar(10),

sal float,

Department varchar(10)

)


http://www.dotnetfunda.com/codes/code2583-inserting-multiple-rows-using-a-single-sql-query-in-sql-server2008.aspx

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 6/11/2012 [Member] Silver | Points: 50

Up
0
Down

Resolved
So we can conclude this way,
For SQLSERVER2005
INSERT INTO  EmployeeMas (Firstname ,Lastname ,sal ,Department )

SELECT 'Raj','aaa',5000,'ERP'
UNION ALL
SELECT 'Ram','bbb',4000,'ERP'
UNION ALL
SELECT 'Sachin','ccc',5000,'MED'
UNION ALL
SELECT 'Rahul','ddd',8000,'MED'


For SQLSERVER 2008
1.The above query works fine both in SQLserver2005 & 2008
2.
insert into  EmployeeMas values('Raj','aaa',5000,'ERP'),('Ram','bbb',4000,'ERP'),('Sachin','ccc',5000,'MED'),('Rahul','ddd',8000,'MED')


Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/7/2012 [Member] Bronze | Points: 25

Up
0
Down
Thank youu..

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ajay.Kalol on: 6/7/2012 [Member] Starter | Points: 25

Up
0
Down
if you want to insert multiple row at a time from .Net,

Then Use
Bulkinsert in ADO.Net

Ajay
ajaypatelfromsanthal.blogspot.in

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/7/2012 [Member] Bronze | Points: 25

Up
0
Down
Thanks

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 6/7/2012 [Member] Silver | Points: 25

Up
0
Down
You are welcome..:-)

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 6/7/2012 [Member] Silver | Points: 25

Up
0
Down
Hope you understand it.
Mark as Answer if its helpful to you,that motivates...

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/7/2012 [Member] Bronze | Points: 25

Up
0
Down
Ok..Thank youu

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 6/7/2012 [Member] Silver | Points: 25

Up
0
Down
Now it will helps the others those who search the same...

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/8/2012 [Member] Bronze | Points: 25

Up
0
Down
Ok..Thank you

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 6/8/2012 [Member] Starter | Points: 25

Up
0
Down
its not working...........

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 6/8/2012 [Member] Silver | Points: 25

Up
0
Down
@Perfect.Chourasia@Gmail.Com
Are you getting any error?Please share the details..

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/8/2012 [Member] Bronze | Points: 25

Up
0
Down
Ok..

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/8/2012 [Member] Bronze | Points: 25

Up
0
Down
Thank youu...
Using this query i inserted multiple rows at a time..



Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 6/8/2012 [Member] Starter | Points: 25

Up
0
Down
create table employee(empname varchar(90),empaddress varchar(90))

insert into employee values('test'),('test1')


hope this will help regards



sriram

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/10/2012 [Member] Bronze | Points: 25

Up
0
Down
Thank youu

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 6/11/2012 [Member] Silver | Points: 25

Up
0
Down
The code posted by me is working fine...!!!

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/11/2012 [Member] Bronze | Points: 25

Up
0
Down
Yes..That code is working fine..

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 6/11/2012 [Member] Starter | Points: 25

Up
0
Down
I m getting Error--


Incorrect syntax near ','.

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/11/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi sandeep,

please check your code. i have work correctly..please use CGN007 code..




Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 6/11/2012 [Member] Silver | Points: 25

Up
0
Down
@Perfect.Chourasia@Gmail.Com
Did you getting the error,While Inserting the data?

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 6/11/2012 [Member] Silver | Points: 25

Up
0
Down
Also which version of SQLSERVER,you are using?

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 6/11/2012 [Member] Starter | Points: 25

Up
0
Down
Table creation Script


create table EmployeeMas
(Firstname varchar(10),
Lastname varchar(10),
sal float,
Department varchar(10)
)
While inserting multiple it gives error..


insert into EmployeeMas values('Raj','aaa',5000,'ERP'),('Ram','bbb',4000,'ERP'),('Sachin','ccc',5000,'MED'),('Rahul','ddd',8000,'MED')

it works fine if i insert single record

insert into EmployeeMas values('Raj','aaa',5000,'ERP')

Error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Sql serserver 2005

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 6/11/2012 [Member] Silver | Points: 25

Up
0
Down
Do you have any possible way to check the same in SQLSERVER2008?
I've execute the same in my SQLSERVER2008,And its works fine.

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 6/11/2012 [Member] Silver | Points: 25

Up
0
Down
In the mean time please try the following query...,
INSERT INTO  EmployeeMas (Firstname ,Lastname ,sal ,Department )

SELECT 'Raj','aaa',5000,'ERP'
UNION ALL
SELECT 'Ram','bbb',4000,'ERP'
UNION ALL
SELECT 'Sachin','ccc',5000,'MED'
UNION ALL
SELECT 'Rahul','ddd',8000,'MED'


Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/11/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi sandeep,
Are u Ok now?Did u get result?

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 6/11/2012 [Member] Starter | Points: 25

Up
0
Down
Yes Union all worked...

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 6/11/2012 [Member] Silver | Points: 25

Up
0
Down
@Muhsinathk
Now you can mark the above post also as answer...

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response