Question

bulk update in SQL Server 2005

BULK UPDATE, SQL Server 2005

I need to updata Column A1, Column A2 data in table A with Column B1, Column B2 data in table B for over 100,000 rows, how to write a procedure to accomplish this in sql server 2005?

Assuming that you have a column with distict values to show you which rows are which between the two tables this can be done with a simple update statement.

UPDATE TableA
    SET TableA.A1 = TableB.B1,
         TableA.A2 = TableB.B2
 FROM TableB
 WHERE TableA.A3 = TableA.B3

If you are worried about creating one massive transaction you can batch the operation into smaller chunks. This is done via the TOP keyword.

UPDATE TOP (1000) TableA
    SET TableA.A1 = TableB.B1,
         TableA.A2 = TableB.B2
 FROM TableB
 WHERE TableA.A3 = TableA.B3
    AND TableA.A1 <> TableB.B1
    AND TableA.A2 <> TableB.B2
 

You can put that into a loop like so.

 DECLARE @d BIT
 SET @d = 1
 
 WHILE @d = 1
 BEGIN
      UPDATE TOP (1000) TableA
         SET TableA.A1 = TableB.B1,
              TableA.A2 = TableB.B2
      FROM TableB
      WHERE TableA.A3 = TableA.B3
         AND TableA.A1 <> TableB.B1
         AND TableA.A2 <> TableB.B2
 
      IF @@ROWCOUNT = 0
           @d = 0
 END