Performant Flat File SQL Insert question

[Red2]

Hi All,
I am receiving a flat file data feed from another system. This is
comming in every 10 minutes and is going to need to be a high
permormance system. The file will contain all the data every time,
which might require a truncate and re-fill of the data (I haven't
figured out if they'll let me update yet, but because of performance
concerns inserts are cheaper then updates) I figure to use
SystemFileWatch to catch the file comming in. Convert it to XML, which
required splitting the flat file up, and re-writting it. Then using
OPENXML to insert it into a database.
Let me know what you guys think. I'm pretty concerned about this
system. The web page that displays the data is going to have high
traffic.
Thanks for any ideas and thoughts

 

[Guest]

For the best performance you'll want to use SqlBulkCopy, have a look at:
http://msdn2.microsoft.com/en-US/library/system.data.sqlclient.sqlbulkcopy(VS.80).aspx

About the OpenXML idea, if you files are large you may run into issues using
this method.

Sayed Ibrahim Hashimi
www.sedodream.com

 

[Red2]

used Bulk Copy, it worked great, thanks. I had to use Typed Data Sets
to assist in gettting everything lined up. SQLBulkCopy doesn't have
really good error handling. But it's fast and easy. Thanks