bulk insert 3M records to SQLExpress

Question

Hi,

While trying to bulk insert 3M records from CSV file to SQLExpress database the procedure throw the timeout exception which was set to 30s. I tried to set the Connect Timeout to 1800 but again the procedure threw the same exception.

Does anyone know whether the exception is thrown due to too many records or the timeout was not set correctly?

Below is the connection string, query statement and row from file

connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=simulatorDB.mdf;Integrated Security=True;Connect Timeout=1800;User Instance=True"

query = "BULK INSERT real_data FROM '" + path + "' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"

AFAB19476C2CEEEE101FFA45FD207BA8B6185B29,539EE0643AFC3A3BE3D20DC6BE7D5376DC536D34,9800,58,29,24,34,2

I would be very thankful if anyone suggested a fix for the described issue.

Thank you!

Answer 1

It's not the connection timeout you need to set - what you need to increase is the Command Timeout.

As for how long importing 3M records will take, it depends exactly on the table you are importing into - i.e. if it's a new table or existing table with some data/indexes already on, whether the table is actively being used by other processes.