How can update a table in pieces

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How can update a table in pieces

Author

Topic

dagobal
Starting Member

4 Posts

Posted - 2005-01-17 : 07:48:39

How can i do update in pieces. For example you have a table with 1000 records and i will do an update with the first 100 records and after that the next 100 records and so on to the end of the table.

can some one help me please..?

DaGo

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-17 : 08:11:05

Need a loop and for that you need a means of splitting up the rows into batches and this will depend on what you are updating.

None of this tested.

e.g.
set rowcount 100
select 1
while @@rowcount <> 0
update tbl set fld = 1 where fld is null

or use an identity
declare @id int
select @id = 0
while @d < (select max(id) from tbl)
begin
select @id = min(id) from tbl where id > @id
update tbl set fld = 1 where id between @id and @id + 100
select @id = @id + 100
end

use the pk of the table and a temp table
select pk1, pk2, identity(int, 1,1) as id into #a
create unique index ix on #a(id)
declare @id int
select @id = 0
while @id < (select max(id) from #a)
begin
update tbl
set fld = 1
from tbl t
join #a
on #a.pk1 = t.pk1
and #a.pk2 = t.pk2
where #a.id between @id and @id + 100
select @id = @id + 100 + 1
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dagobal
Starting Member

4 Posts

Posted - 2005-01-17 : 08:36:23

nr first thanks for the answer.
I will tell you the original reason. I have a table with 160 mil. records. first i want to look from this table for some data en put it on a small lookup table after i want to update the big table with the small lookup table.
The big problem is performance because it makes a transaction log.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-17 : 13:53:46

Any of the techniques above could work - depends on the data.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dagobal
Starting Member

4 Posts

Posted - 2005-01-18 : 06:16:30

Hi nr,
The second one works fine but i have to use the last one.
Can you explain de two lines for me please:

select pk1, pk2, identity(int, 1,1) as id into #a
create unique index ix on #a(id)

thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources