October 31, 2002 at 9:48 pm
I am having requirement in which i have to insert near about 500000 records , so i hav written stored procedure , requirement is such that use of INSERT INTO clause is possible. I tried directly inserting records in one shot , it gives time out . Then i tried 50000 record per iteration and having 3 iteration in stored procedure then also time out occures
November 1, 2002 at 4:27 am
Can you give us an example of what you are doing and have you tried it with 1 record. Also, depending on the format you were sent the data in have you looked at DTS to import the data?
November 1, 2002 at 7:19 am
Also, If your insert involves a Remote/Linked server, there is a RPC Timeout setting under Server->Properties->Connections->Query time-out.
-Dan
-Dan
November 1, 2002 at 2:34 pm
If you are using ADO you will have to increase the command timeout on the command object.
From experience it is best to insert data in bacthes of ~ 2000. So start a transaction do 2000 inserts and the commmit. This does have the problem if you want to rollback the whole lot.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply