bulk processing - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

bulk processing

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-05-24 : 16:08:07

Greetings

I am dealing with million of rows of data. How can use

ROW_NUMBER () function in a CTE to select e.g. 100.000 records at a time?

Thanks!!!

If you don't have the passion to help people, you have no passion

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 16:16:17

with cte as
(
select *, seq = row_number() over (order by myuniquefld)
)
select *
from cte where seq between 1 and 100000-1

I would consider other methods as well.

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

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-05-24 : 23:09:32

thanks! But how do I iterate through 100,000 records at a time? so I can process destination table rows?

I am doing a between using identity fields and using MERGE. But with MERGE if you source query is big it takes a long time to process.

If you don't have the passion to help people, you have no passion

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-05-25 : 03:37:44

I think this should work... ->

WHILE (...)
  BEGIN
    MERGE myTable AS target
    USING (SELECT TOP 100000 Col1, Col2 FROM myOtherTable) AS source (Col1, Col2)
        ON (target.Col1 = source.Col1 AND target.Col2 = source.Col2)
    ...

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-25 : 07:40:51

Identity is better than rownumber
declare @i int, @imax int
select @i = 1, @imax = max(id) from tbl
while @i < @imax
begin
with cte as
(
select * from tbl where id bewteen @i and @i + 100000-1
)
merge newtbl t1
using cte t2
on t1....= t2...
...
;
select @i = @i + 100000
end

tbl would probably need a clustered index on id.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources