[thelist] simple method for uploading CSV files to MSSQL

Paul Cowan evolt at funkwit.com
Mon Aug 4 19:18:55 CDT 2003


.jeff wrote:
> give em a file upload and then do a bulk insert.

Ooh, this calls for an important SQL-type tip:

<tip type="MS SQL" author="Paul Cowan">
In some circumstances, MS SQL server BULK INSERTs are non-logged
operations[1]. Basically, this means that the details of the inserted rows
are not kept in the transaction log, which improves performance, but
also means that if your DB dies, and you need to go to backups and roll
forward through the day's transaction log to get to the point just before
the crash... well, you won't be able to.

Once there's a non-logged operation in a transaction log, you can't
restore backups of that log (in fact, you can't back it up), until
you've done your next full DB backup. Your transaction logs are
effectively useless, in other words.

SELECT INTO does the same thing -- can bite you on the posterior in a big
way. Be careful.

[1] but not all. There are about five things[2] which determine if a bulk
insert is logged or non-logged.
[2] don't ask me, I don't understand them.
</tip>

Paul


More information about the thelist mailing list