Need script to UPDATE 1000 Rows then COMMIT, Iteratively

Question

Post reply

Need script to UPDATE 1000 Rows then COMMIT, Iteratively

Express12

SSCoach

Points: 18514
More actions
February 13, 2007 at 1:05 pm

#116126

In search of a T-SQL script that will UPDATE 1 M rows on a table, invoking a COMMIT every 1000 UPDATES
(for business reasons, I cannot use an IDENTITY column here.. too much detail to divuldge) ---
I basically want to:
   DECLARE MyCounter INT
   SET MyCounter = 1
   UPDATE MyTable SET MyColumn = MyCounter + 1
   SET MyCounter = MyCounter + 1
   COMMIT After every 1000 th UPDATE.... WITHOUT LOSING CURRENCY POINTER TO MY DATA
BT

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

What's the order in which you need to base this counter?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

If there something that is preventing you from writing the script that you need?

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 3

No matter which way you do it, we're going to need to know what the primary key for the table is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stax68 SSChampion Points: 11711 More actions · Answer 4

Just to be a bit pedantic, the + 1 here:
UPDATE MyTable SET MyColumn = MyCounter + 1
means you will start your numbering at 2.

A couple of questions (1 has already been asked):

1. Do these need to be in any particular order?

2. Is the column currently empty or populated with 0s?

If 1 = No and 2 = Yes, then you could do something like:

declare @i
select @i = 1000, @j-2 = 0

while @i = 1000 and j < 1000000
begin

update top 1000 m
set @j-2 = @j-2 + 1, MyColumn = @j
from MyTable m
where m.MyColumn = 0

select @i = @@rowcount

end

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

IIRC UPDATE top 1000 doesn't work with sql 7 and 2000.

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 6

Ok... in the absence of a response about what the primary key might be...

First... no loops... think set based... loops just slow things down.

Second, I can only believe that you want to do this a lousy 1,000 rows at a time because you think this type of numbering is going to be slow on a little ol' million row table...

Here's some test code to show just how fast this can be done... without loops either... the code in blue is what does all the work... the rest is just test setup and verification...

--===== If the demonstration table exists, drop it

IF OBJECT_ID('TempDB.dbo.#MyHead') IS NOT NULL

DROP TABLE #MyHead

GO

--===== Create the demonstration table

CREATE TABLE #MyHead

(

RowNum INT, --<<This will be the column we update

SomeValue INT

)

--===== Populate the table with 1,000,000 rows worth of random numbers (SomeValue)

-- to simulate some form of data

INSERT INTO #MyHead (SomeValue)

SELECT TOP 1000000

RAND(CAST(NEWID() AS VARBINARY))*2147483647+1

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--=========================================================================

--===== Ok, our demo table is setup and we're ready to test

--=========================================================================

--===== Declare a counter variable...

DECLARE @MyCounter INT

SET @MyCounter = 0

--===== Do the update with sequential numbers...LOOK MA!!! NO LOOP!!

-- On my desktop box, it only takes 6 seconds to complete.

UPDATE #MyHead

SET @MyCounter = RowNum = @MyCounter + 1

--===== Now, let's check the table...

SELECT * FROM #MyHead ORDER BY RowNum

If you have a clustered primary key on the table, the numbers will be in that order.

Please lemme know if this helps...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

schleep SSChampion Points: 12585 More actions · Answer 7

Jeff,

VERY COOL!!!!

Never seen that UPDATE...SET construct before.

Not so fast here, tho, took 1m11s.

Thanks,

Paul

Harley Arnett-275660 SSC Eights! Points: 859 More actions · Answer 8

Jeff,

Brilliant solution.......is there a way to perform similar set-based magic in select statements - without using a temp table or joining to a counter table - I can't tell you how many times I have been asked to return a result set with a sequential number in each row.

Harley

Farhad-386799 Say Hey Kid Points: 681 More actions · Answer 9

Hello,

The best way to accomplish this task is to write a script and I do not think there is a way where you can use Update to update based on counter. Unless, your table has a sequential number, but this requries manaul update.

use cursor and counter. Test it in dev first.

Hope this helps.

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 10

Heh... thanks, Paul... I always wonder if anyone reads these things...

Just so you know I'm not using some whacko, back door, undocumented construct, here it is from Books OnLine...

UPDATE

Changes existing data in a table.

Syntax

UPDATE

{

table_name WITH ( < table_hint_limited > [ ...n ] )

| view_name

| rowset_function_limited

}

SET

{ column_name = { expression | DEFAULT | NULL }

| @variable = expression

| @variable = column = expression } [ ,...n ]

{ { [ FROM { < table_source > } [ ,...n ] ]

[ WHERE

< search_condition > ] }

You should see what it does for balancing about a million check books for a bank! You can do running totals, grouped running totals, running counts, grouped running counts, ranking, etc, etc, with it and it's darned fast in all cases. You DO have to be a bit careful, though... if the indexes aren't just right, "Death by SQL". I normally copy the data I need into either a dedicated working table or a temp table (depending on the size of the data) where I have full and exclusive use of the data and the clustered index.

Hey, I'm curious about the 1m 11s time your box took and what the differences may be. I'm running SQL Server Developer 2000 sp4 on a 1.8Ghz single cpu and 2G of ram with what was considered to be a "server quality" IDE drive controller (lots of cache but I forget how much). My DB is setup for "Simple" recovery (I don't need to back anything up). The whole shootin' match including the table creation and final SELECT (grid mode, not text mode) in my demo only takes 33 seconds and, like the code comments say, the UPDATE only takes 6 seconds. What kind of box are your running on, what's the rev of SQL, and what's the recovery mode set to?

Anyway, you and Harley made my day... thanks, Paul.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 11

Hey Paul,

I almost forgot a trick with using this if you can't make a "sister table".... you can force the order by using an index hint... like this... (this is a count grouped by customer)

UPDATE Orders

SET @Count = OrderCount = CASE WHEN @CustNo <> CustNo THEN 1 ELSE @Count+1 END,

@CustNo = CustNo

FROM Orders WITH (INDEX(Orders_CustNo_OrderDate )) --THIS FORCES THE CORRECT INDEX TO BE USED SO YOU DON'T NEED TO USE A CLUSTERED KEY!!!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

You could also use a temp table to doe this:

create table #temp
(
MyTableID int  not null primary key clustered,
Counter  int not null identity(1,1)
)

Inset into #temp (MyTableID)
select top 100 percent
 MyTableID
from
 MYTable
order by
 MYTableID

update MYTable
set
 MyCounter = #temp.Counter
from
 MYTable
 join
 #temp
 on MYTable.MYTableID = #temp.MYTableID

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 13

I do not think there is a way where you can use Update to update based on counter

I agree that there's no way to do it with a SELECT in SQL Server 2000 but take a look at the code I posted...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 14

Hey there Harley,

Thanks for the compliment... You and Paul made my day!

Sadly, in SQL Server 2000, there's no way to return the equivelent of row numbers using a SELECT without using the methods you stated, a WHILE loop, a triangular join (can be thousands of times slower than a cursor!) or a Cursor. Personally, I prefer something like...

SELECT IDENTITY(INT,1,1) AS RowNum,

... other columns in the result set ...

INTO #sometemptable or someworkingtable

FROM yada-yada

There are some reported problems with some minor blocking on some of the sys tables using this method but, in most cases and especially if the code is fast and the result set is relatively small (< million rows), I've not found blocking to be an issue especially for batch code (as opposed to GUI code where lot's of users may be trying to do the same thing). If you do find blocking to be an issue, then create the table first and then do an Insert/Select... 'course, judging from your post, you already knew that but I had to say it just so I feel better about it

The trick with a join on a numbers table is really fast but, as you're aware, if you run out of numbers in the numbers table, ya kinda get stuck (Michael Valentine Jones has a function that very handily gets around that... here's the link... it's VERY fast).

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

So far as using a WHILE loop or Cursor? I try not to use them for RBAR (pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row") like this except to demo how slow they can be and how many resources they use. There are exceptions using the WHILE loop in a function but I've never put a cursor into production.

If you have SQL Server 2005, the good news is that SQL Server now has a RowNum like Oracle does. Why they didn't do that way back in 6.5 is beyond me.

Anyway, thanks again for the compliment, Harley.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)