467,212 Members | 1,127 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,212 developers. It's quick & easy.

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

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 filethe "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
  • viewed: 1601
Share:

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Craig Stadler | last post: by
1 post views Thread by gchavez@aspenres.com | last post: by
1 post views Thread by Metal Dave | last post: by
9 posts views Thread by Hemant Shah | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.