Thanks Bonnie.

I discovered the command builder it is not really capable of batch updates. It just fires off the same UPDATE command multiple times so I am not trying to use it for this process.

What would be nice is an SP that receives the following paramters.

1. Target Table to be updated
2. Source Table containing the values that need updating. (Not sure how to send to the SP)
3. Flag indicating if records missing from the source table are to be deleted from the target.

The code would assume that fields names are common between the tables but will allow the source table to contain less fields than the traget table.

The pk field would be established by using some built in SQL funtion.

The SP would assume that source records that have a blank pk are to be added.

Have you ever come across an SP like this. How would I send my source table to the SP?
This is all beyond my current abilities of SP creation, on this job anyay:)

Would it be possible for you to give me the code for one of your more simple PUT SP's ?
This would give me a leg up on writing my own SP's, and eventually I should be able to create
my desired batch updater myself.





>Mark,
>
>When you say you need an UPDATE command and then an INSERT command, are you talking about actual SQL commands or are you talking about the SQL stuff that the CommandBuilder generates? Actually, I don't use the CommandBuilder, and I think it's better not to, but sometimes I think I'm in the minority with that opinion. But, I digress .... If you're talking about SQL Server commands, then let me suggest something ... I use Stored Procs for all my data access. I have basic GET, PUT and DELETE SP's for each table in my database. The PUT SP's handle both updates and inserts, depending on what's in the PK that gets passed to it. If the PK has a value in it, the row is updated, if not, it's inserted and the new PK is returned as an output parameter.
>
>In any case, yes, you have to do this for each row that has changes in your DataTable. Even ADO.NET's .Update() method does it that way (behind the scenes). The only batch update that I'm aware of would utilize the new XML features of SQL Server and would probably get into DiffGrams, but I have no experience with that, so I can't help you there.
>
>~~Bonnie
>
>
>>Hello
>>
>>I am new to both ADO and SQL Server
>>
>>I have an ADO table as follows
>>
>>
>>1 Apple Green
>>? Apple Red
>>? Pear
>>? Peach
>>5 Grape Red
>>
>>
>>I have sql table as follows, which needs to be updated from the ADO table above (which was not directly derived from the SQL table)
>>
>>1 Apple
>>2 Banna
>>3 Melon
>>5 Grape
>>6 loganberry
>>
>>
>>As can be seen Apple in the SQL table must be changed to Apple Green and Grape to Grape Red, also 3 new records need to be added.
>>
>>At present it appears I will have to run the UPDATE command for for each ado.row that I have a PK for (indicating old existing records) and then run an INSERT command for each ado row that does not have a PK (indicating it is new). I had hoped there would be an option to the UPDATE command would add the records if they did not exist.
>>
>>
>>I expect there is a much easier way of doing this and I am too dumb to find it in the SQL Server Help. The obvious approach is to pass the entire ado table to SQL Server and have it use some internal (batch update?) function to do all this at the back end.
>>
>>In some cases it would be nice if I could pass an entire snapshot of the the ado data table and have all updates done on the SQL Table including deletions.
>>
>>If not is their anyway of passing an entire ADO table straight to SQL Server to be used from an SP. Is this what the new "Table Data Type" is about.