How to Solve this Sql Bulk Insert - Bandi

Posted by Jayakumars under ASP.NET AJAX on 5/4/2015 | Points: 10 | Views : 652 | Status : [Member] [MVP] | Replies : 5
Hi
Bandi

I have table in 7 records then i need insert same table below requirement

CReate Table Emp_DET(
EID INT IDENTITY(1,1),
YEARID int,
NAME VARCHAR(20),
OID int,
FID int
)

Insert into Emp_DET VALUES(1,'AA',1,2)
Insert into Emp_DET VALUES(1,'AA1',1,3)
Insert into Emp_DET VALUES(1,'AA2',1,4)
Insert into Emp_DET VALUES(1,'AA3',1,5)
Insert into Emp_DET VALUES(1,'AA4',1,6)
Insert into Emp_DET VALUES(1,'AA5',1,7)
Insert into Emp_DET VALUES(1,'AA6',1,8)



Select * from Emp_DET

EID YEARID NAME OID FID
1 1 AA 1 2
2 1 AA1 1 3
3 1 AA2 1 4
4 1 AA3 1 5
5 1 AA4 1 6
6 1 AA5 1 7
7 1 AA6 1 8

-- This is Real Records here 7 Records
-- But I need same table insert for some values changes like this

--My output Like this

EID YEARID NAME OID FID
1 2 AA 21 2
2 2 AA1 21 3
3 2 AA2 21 4
4 2 AA3 21 5
5 2 AA4 21 6
6 2 AA5 21 7
7 2 AA6 21 8

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com



Responses

Posted by: Bandi on: 5/4/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
insert Emp_DET 
select YEARID+1, Name, cast(YearID+1 as varchar)+cast(OID as varchar), FID from Emp_DET


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Posted by: Jayakumars on: 5/4/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

I meet this issue

Error1
========

Explicit value must be specified for identity column in table 'EID'
either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.


How to particular column only insert



Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

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

Posted by: Bandi on: 5/4/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
have you executed the above given code? or do you changed any thing?

If NOT, then you may have INSTEAD OF TRIGGER on table EMP_DET...

Try the below code ,

insert Emp_DET (YEARID,	NAME,	OID,	FID)
select YEARID+1, Name, cast(YearID+1 as varchar)+cast(OID as varchar), FID from Emp_DET


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Posted by: Jayakumars on: 5/4/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
I have Large amount Records . So I meet this issue from my c# code.

Error1
===========
Error while getting admissions status.Timeout expired.
The timeout period elapsed prior to completion of the operation or the server is not responding

How to solve but i tried set timeout=300 but my insert and fetch records methods 1000 methods have
so how to solve timeout issue.

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

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

Posted by: Bandi on: 5/4/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
If there are very huge amount of data to do in single shot, better to go with Batch-wise insertion to prevent TIMEOUT error...

INSERT TableName
SELECT columnsNames FROM TABLENAME
WHERE ID BETWEEN 1 AND 10000
GO
INSERT TableName
SELECT columnsNames FROM TABLENAME
WHERE ID BETWEEN 10001 AND 20000

and so on....


NOTE: am assuming that the purpose of above querying is only for inserting dummy data for testing or creating sample data...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Login to post response