SQL Server : update multiple rows dynamically

sqlsql serversql-server-2005sql-updatestored-procedures

I want to update multiple rows in my database, the values might be different. Imagine this table:

Username   FirstName   LastName 
-------------------------------
user1      John        Doe 
user2      Jane        Doe
user3      bill        gates

If I want to update the table so it looks like this:

Username   FirstName   LastName 
-------------------------------
user1      John        Deer 
user2      Jane        Farmer
user3      Gill        Bates

Is it best to use one UPDATE statement for each user? Or is it possible to do this with one query?

Also is it possible to put it in a stored procedure and provide a collection of values?

Second question is it best to check if the values are changed before doing an update? Or just update everyone even if the value is the same?

I am currently using SQL Server 2005 and C# for the application if that is relevant.

Best Answer

You can try this

declare @TempTable1 table (UserName nvarchar(128) primary key, FirstName nvarchar(128), LastName(128))

insert into @TempTable1
select 'user1', 'John', 'Deer' union all
select 'user2', 'Jane', 'Farmer' union all
select 'user3', 'Gill', 'Bate'

update table1 set
    FirstName = t2.FirstName,
    LastName = t2.LastName
from table1 as t
    inner join @TempTable1 as t2 on t2.UserName = t.UserName

Or if you want to update only changed fields

update table1 set
    FirstName = isnull(t2.FirstName, t.FirstName),
    LastName = isnull(t2.LastName, t.LastName)
from table1 as t
    inner join @TempTable1 as t2 on t2.UserName = t.UserName

For C# client application I think usual way is to create procedure

create procedure sp_Table1_Update
(
    @UserName nvarchar(128),
    @FirstName nvarchar(128),
    @LastName nvarchar(128)
)
as
begin
    update table1 set
        FirstName = @FirstName,
        LastName = @LastName
    where UserName = @UserName
end

and then call it from your application for each user

Related Topic