Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-08 : 17:08:12
|
| John writes "Hello,On a fairly regular basis I have an application that needs to grab a load of records from one table and insert them into another. Currently I am using a query like: INSERT INTO TABLE_NAME (Cols) SELECT Cols FROM OTHER_TABLEOccasionally this process will need to copy around 100,000 records and it will sometimes fail with a Microsoft SQL Timeout expired error. Can you explain why sometimes a query will happily run and run for many minutes and not fail, and on other occasions the same query to insert the same amount of records will timeout very quickly?Also can you suggest a better method of doing this big insert? I have looked at BULK INSERT but you have to generate a file to import from, plus it seems to lock the table I import to and won’t allow another query to update older records in the same table.I hope you can help.ThanksJohn" |
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-08-08 : 17:24:22
|
| This could be related to blocking problems. Have you used Profiler to monitor blocking on your tables? How about Index Tuning Wizard to determine whether you have the right indexes in place?----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-08-09 : 10:08:40
|
| Are other processes happening at the same time? I am referring to auto backup routines, or DTS packages that might be burning system resources. AjHey, it compiles. |
|
|
|
|
|
|
Timeouts inserting a very large number of records