February 20, 2004 at 8:31 pm
I am a newbie to sqlserver and dts. I have 32,000 byte records coming from a mainframe system in flat files. Trying to load into sql server 2000 tables. currently using custom(ugly) perl script to break into manageable pieces and then using dts to load into sql server. Any other suggestions ?
February 23, 2004 at 1:26 pm
You may want to look at using bcp which can run in fast (not logged) or logged mode. Fast mode is good for large amounts of data not only because its faster but since it isn't logged you won't run out of transaction log space. Put you database into Simple recovery mode first. To run in fast mode:
1. Drop indexes
2. Drop any triggers
an alternative to 1 and 2 above is to execute the following:
sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
3. Run bcp
4. Re-index the target table
5. Recreate the triggers (or to re-enable use sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? CHECK CONSTRAINT all"
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER all"
If you've added a lot of stuff you may want to update statistics for this table. Lastly put the database back to Full recovery mode. This whole process can be automated
Francis
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply