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.