batch processing for non-sequential id

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)

batch processing for non-sequential id

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-05-19 : 18:30:43

Greetings

I have to update a table with new data based on another table. I need to do it in batches as there are millions of records.

Now the source table does not necessarily have sequential id to use to map to dest table. So how do I go about creating a while fetch status so I can process 1000 rows at a time. using >= and <=?

Thanks!!!

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-19 : 18:39:49

There are other things you can batch on, like dates, departments, etc. Does your source table have anything that lends itself to a BETWEEN statement?

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-19 : 19:16:47

You could also make use of a cte (or temp table) where you add the arbitrary sequential id, e.g.

;with cte as (select [id] = row_number(),sourceColumn, etc.)
UPDATE tgt
SET tgtColumn = sourceColumn
FROM targetTable tgt
INNER JOIN cte c on tgt.xxx = c.xxx
WHERE c.id between @here and @there

set @here = @there + 1
set @there = @here + 10000

Jim

Everyday I learn something that somebody else already knew

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-05-19 : 19:31:12

thanks Jim let me try that out! all i have is Ids I can do between.

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

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-05-20 : 17:03:50

if anyone can use this and improve it

it just loops through a certain table and grabs the Id you specify and stuffs them into a table/table variable. Then you can do a table valued function to join to the value returned.
it was very helpful to me



declare @start int
declare @end int
declare @end_of_top_1000 int
declare @inc int
declare @count int
declare @strin_of_all_ids nvarchar(max)
declare @strin_of_ids nvarchar(max)
declare @finished bit
declare @concat_table table(Items nvarchar(max))
declare @Items nvarchar(max)

SET @finished = 0

SET @start = 0
SET @end = 0
SET @inc = 0

  
SELECT top 1 @start =  YourTable_id
  FROM dbo.YourTable
ORDER BY YourTable_idASC  


SELECT top 1 @end=  ourTable_id
  FROM dbo.YourTable
ORDER BY YourTable_idDESC 

SELECT top 1 @end_of_top_1000 = YourTable_id
   FROM
(
SELECT YourTable_id ROW_NUMBER() OVER(ORDER BY YourTable_idASC) as rownum
  FROM dbo.YourTable
) a
Where rownum = 1000

PRINT '@start ' + cast(@start as nvarchar(max))
PRINT '@end ' + cast(@end as nvarchar(max))
PRINT '@end_of_top_1000 ' + cast(@end_of_top_1000 as nvarchar(max))
PRINT '@inc ' + cast(@inc as nvarchar(max))

SET @inc = 1000

WHILE @finished = 0
    BEGIN

		SELECT @strin_of_all_ids = @strin_of_all_ids  + cast(YourTable_id as nvarchar(max)) + ', '
		  FROM dbo.YourTable
		 WHERE YourTable_id  >= @start and YourTable_id <= @end_of_top_1000

				
		PRINT @strin_of_all_ids


		SET @start = @end_of_top_1000

		IF @inc > @end
			BEGIN
				SET @inc = @end
			END		
		
		SELECT top 1 @end_of_top_1000 = YourTable_id 
		   FROM
		(
		SELECT YourTable_id , ROW_NUMBER() OVER(ORDER BY YourTable_id  ASC) as rownum
		  FROM dbo.YourTable
		) a
		Where rownum >= @inc 

		SET @inc = @inc + 1000
		
		PRINT '@start ' + cast(@start as nvarchar(max))
		PRINT '@end ' + cast(@end as nvarchar(max))
		PRINT '@end_of_top_1000 ' + cast(@end_of_top_1000 as nvarchar(max))
		PRINT '@inc ' + cast(@inc as nvarchar(max))
		
		INSERT INTO @concat_table
		SELECT @strin_of_all_ids
		
		SET @strin_of_all_ids = '' 
		
		--IF @end_of_top_1000 >= @end
		IF @inc > @end
			BEGIN
				PRINT '@start ' + cast(@start as nvarchar(max))
				PRINT '@end ' + cast(@end as nvarchar(max))
				PRINT '@end_of_top_1000 ' + cast(@end_of_top_1000 as nvarchar(max))
				PRINT '@inc ' + cast(@inc as nvarchar(max))
				SET @finished = 1
			END
    END;

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources