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 |
|
WaterWolf
Starting Member
24 Posts |
 Posted - 2009-02-03 : 06:20:35
|
| Hello,I have a external programme that is using bulk insert to enter a large amount of data into my database. For whatever reason this is not updating the indexes correctly on the table. I want to rebuild the indexes on the table after the bulk insert.I could use a trigger which uses AFTER INSERT but I suspect that will fire after every line is inserted rather than when the bulk insert has completed. Is there any way of rebuilding the indexes after this bulk insert without editing the exterior programme ? |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-02-03 : 06:36:55
|
| Having a trigger updating indexes is a h.o.r.r.i.b.l.e. idea!! But you can read about bulk insert and trigger behaviour here:-> http://msdn.microsoft.com/en-us/library/ms187640(SQL.90).aspxIs it possible that you schedule an index rebuild instead after the bulk insert is finished? Is this bulk insert scheduled or does it happen at random intervals?- Lumbago |
 |
|
|
WaterWolf
Starting Member
24 Posts |
Posted - 2009-02-03 : 08:35:40
|
| Well looking at that page it seems that bulk insert disables triggers unless you change its settings - which would mean changing the external programme.The bulk insert is triggered by a user pressing a button on a webpage. The process takes about 30 seconds anyway so I thought a couple of seconds spend rebuilding indexes at the end wouldn't make much difference.The problem seems to be that the indexes don't update for the latest data that's inserted into the table. Selecting this data can take over 60 seconds whereas selecting anything else from the table is instantaneous. If another bulk insert is performed then the previously inserted data that was unindexed is then indexed but the new stuff is not.No amount of fiddling around with indexes seems to have fixed this so I'm trying to find a way to rebuild the indexes without having to change code. |
|
|
|
|
|
|
Trigger after bulk insert