UpdateBatch()

UpdateBatch()

Post by Stephen How » Sat, 08 Jan 2000 04:00:00



Hello,

I have this situation where I am a loading data into many tables from CSV
files using ADO & VC++. It is never likely to be more than 8000 records.
UpdateBatch() seems natural to do this.

Now, for some of the rows the data is new, for other rows it is an update as
as the primark key does not allow duplicates.

As far as I can see there are two methods of doing this (in pseudo-code)

1.  Open the RecordSet as a adCmdTableDirect in adLockBatchOptimistic lock
mode
     Work out what the primary key is for the table by inspecting the fields
attributes
     For each record
        Use a Filter to see if the record already exists if the primary key
is more than field otherwise do a Find.
        If the record does exist, update the non-primary fields otherwise do
a AddNew()
        Update()
     Next

     Call UpdateBatch()

2. Open the RecordSet as a adCmdTableDirect in adLockBatchOptimistic lock
mode
     For each record
        Use AddNew()
        Update()
     Next
     Call UpdateBatch()

     Now set Filter to adFilterConflictingRecords which should restrict the
Recordset to all those records that failed because the primary key was
already present. Process these, locate the matching record, change this and
Delete() the duplicate.

    Resubmit UpdateBatch(). Should now amend matching records.

I have opted for the 2nd method but it seems extraordinarily heavy handed
approach to do what seems simple. Have I missed something obvious (how I
open the RecordSet perhaps)? I have seen Microsoft's example VC++ code on
ADO's UpdateBatch() but it assumes that there are no conflicts.

Thanks

Stephen Howe

 
 
 

1. UNIQUE constraint - UpdateBatch

Hi,

Using SQL server 2000 and ADO2.6, UpdateBatch raises an error when i switch
the values of a unique column of two records.
When i delete the UNIQUE contraint from the table UpdateBatch succeeds!

Am I missing a way to control the way ADO handles this kind of conflict ?
any suggestions how to make it work WITHOUT deleteing the UNIQUE constraint

2. Query tuning experts: Any hope for tuning query with 5+ joins and a sort?

3. SQL2000 ADO error in UpdateBatch, from connection conflict?

4. Foxpro Newsgroup

5. Delete ? with UpdateBatch from Union Select

6. Free T1's w/cybercafe sale

7. Strange use of the UpdateBatch

8. ADO Recordset.UpdateBatch

9. Please help with updatebatch problem...

10. SQL Server 7.0 and ADO Batch Updating (UpdateBatch)

11. UpdateBatch question

12. ADO UpdateBatch on left join records