How to Solve this Sql Bulk Insert - Bandi

Posted by Jayakumars under ASP.NET AJAX on 5/4/2015 | Points: 10 | Views : 520 | 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


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

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

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