ADO.net, asp.net & Bulk Copy

  • Thread starter Thread starter Francis
  • Start date Start date
F

Francis

Hi everyone,
Just wondering if someone could help me. I have a CSV file of around
100,000 entries. The application I am building will accept a CSV upload
from the user and insert it into the database.
I noticed, using the normal insert into a Data Table, then out into the
database was very slow. I looked round and found that bulk copying would
be the best way. However. I have 2 questions:
1. Is there a way of uploading a file to the sql server from another PC?
2. Is it possible to manipulate the data before bulk inserting?
3. Is it possible to just bulk insert the data from a Data Table/ Data Set?

Thank you
Francis
 
1. Is there a way of uploading a file to the sql
Click to expand...
server from another PC?

Yes. You can use bulk insert text file to Db:

command.CommandText = "BULK INSERT db_table_name
FROM 'filepath\filename' WITH
(DATAFILETYPE = 'char', FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')"
command.ExecuteNonQuery()
2. Is it possible to manipulate the data before
Click to expand...
bulk inserting?
Yes. But you need read data to datatable first.
3. Is it possible to just bulk insert the data
Click to expand...
from a Data Table/ Data Set?
In ADO.NET 2.0, SqlBulkCopy can write dataset to db
directly. In ADO.NET 1.X, you need implement your code.
Look at following URL
http://support.microsoft.com/kb/315968/EN-US/

HTH

Elton Wang
(e-mail address removed)
 
Hi,
Thanks for the reply. Just trying that out now, but getting the error:
"You do not have permission to use the BULK INSERT statement" although I
am setup as db_owner.
Thanks for your help.
 
Back
Top