Any fast way to import 2GB CSV file into SQL Server 2008?

0
votes

I am new to the database, I am using ssms 2008r2 to import 2GB data from CSV file.

It is taking around 115mins to import.

It has 9-columns without any constraint, no indexes, no trigger. It's a simple table.

Code:

BULK INSERT MyTempfile
FROM 'C:\CSVData\cvfile.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

Is there a better way to reduce the time for this import?

2
sql-serversql-server-2008csv
Better way than what, you never told us how you are doing it now. – Magnus
Can you give us your table definition ? Especially column types – Hybris95

2 Answers

2
votes

Some suggestions:

  1. Disable any referential integrity and auto key insert columns for the duration of the import and re-enable once done
  2. If there are triggers, see if it is possible to disable triggers for the duration and enable afterwards. Of course it may not be possible to disable triggers as they may contain logic.
  3. Chop the file up into smaller chunks
  4. Only import columns that are necessary
0
votes

Make sure your database is in SIMPLE recovery mode.

You can use the following command to switch to SIMPLE recovery mode.

ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE WITH NO_WAIT

Make sure you switch back to the old recovery mode after the operation,
and make a new FULL Backup afterwards.