Updating multiple rows at a time in sql server
Greetings, Fairly new to c# and Im looking for pointers on a few topics: Im curious as to general methods to update multiple rows in a database.Currently Im thinking of just allowing a user to update one row of data at a time and designing/building textboxes to allow them to enter this data. (sql Command) Is there a more efficient method of updating 10 rows of data at a time?Purchase Order Detail AFTER INSERT AS UPDATE Purchase Order Header SET Sub Total = Sub Total Line Total FROM inserted WHERE Purchase Order Header. Purchase Order ID ; -- Trigger is valid for multirow and single-row inserts.USE Adventure Works2012; GO CREATE TRIGGER New PODetail2 ON Purchasing.The first version of the DML trigger works well for a single-row insert when a row of data is loaded into the -- Trigger is valid for single-row inserts.USE Adventure Works2012; GO CREATE TRIGGER New PODetail ON Purchasing.This how-to can possibly ruin a database if done wrong, so make sure that you first test it using toy data (and make sure it works properly). Please feel free to point out any typos and mistakes in the comments below.This is a summary (for myself) of the solution found at Karl Rixon's page here.
Thanks much, Christopher It used to be that I would recommend sending a large group of Stored Proc calls to SQL Server in a single "batch" but the possibility of SQL Injection attacks has brought those days to an end.
Such that if I display the data in lets say a datagrid that they can edit the data and I can programatically add this to the DB?
Along the same lines , what methods in general would be available to distinguish between data being updated as compared to inserted? In my past programming , this wouldve fallen on the programmer to decide(logic) if the row was present in the DB and to update if not to insert.
Well to update mutiple rows in SQL you use an update statement and a where clause if the rows have something in common.
update emptab set settingname = 'blah blah', value = 'bla bla' where (condition) Or if they don't have something in common you'd have to loop through and perform an ado command for every row you wanted to update.