By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,947 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,947 IT Pros & Developers. It's quick & easy.

Trying to find a better way to handle bulk inserts and updates

P: n/a
I have been in search of a better way to handle bulk inserts and
updates into SQL 2000 using c# and while I have found a few different
ways to accomplish this I was wondering what is considered to be the
"BEST" method.

Scenario:
Every day I have a CSV file which is intended to both update and add
records to a SQL table. There is a matching ID column in the CSV file
and in the SQL table, when a match is made an update is performed,
otherwise it's an insert.

Possible Solutions I've explored:

OPENXML (T-SQL)
This method looks like the holy grail of bulk imports and updates but
it seems to be limited by the varchar limitation of 4000 characters.
(I haven't explored whether or not I could use an actual xml file
instead of a variable.) It also seems to be real picky about the data
that comes in via the CSV file—the "prepare XML" procedure fails
often.

BCP utility
Looks like this is great for imports or exports but not for updates

Bulk Insert
Here again only appropriate for inserts not updates.

DATASET method
Read the CSV file into a data set and then send it back into SQL via
the Dataset.Update method. Couldn't make this one work and I'm not
sure it's even possible. Tried something like the following:

OleDbConnection myConn = new OleDbConnection(sConn);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
myDataAdapter.SelectCommand = new OleDbCommand("select * from
Customers", myConn);
OleDbCommandBuilder custCB = new OleDbCommandBuilder(myDataAdapter);
myConn.Open();
DataSet custDS = new DataSet();
myDataAdapter.Fill(custDS);
custDS.Merge(MyDataset); // My dataset from the CSV file – columns
match DB
myDataAdapter.Update(custDS);
myConn.Close();

Again this handles the insert, not the update.

Are there other methods I'm missing or should I go on reading each
record, checking to see if it exists and then performing either an
insert or an update. It just seems like there should/might be a better
way than dealing with each individual record.

Thanks,

Eric
Nov 16 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.