Super_Grover (10/23/2016)
Phil Parkin (10/23/2016)
Super_Grover (10/23/2016)
Phil Parkin (10/23/2016)
Is doc_number unique?It is unique AFAIKS but it's not an unbroken sequnece. In other words: it does not stay in sync with the row numbers (doc_numbers are missing so to say)
OK, then you could easily create a temp table with an identity column and insert all of the doc_numbers into that. Then join from the temp table on doc_number and you have your numbering scheme.
Untested code to get you started
Create table #doc_numbers(RowNum int identity(1,1) primary key clustered, doc_number nvarchar(20) not null)insert #doc_numbers (doc_number)
select doc_number
from tbl
Thank you Phil. But wouldn't that cost me even more diskspace by creating and filling another table?
I think I'm more looking for a way do the INSERT INTO SELECT FROM in small batches.
I think you missed my point somewhat. Yes, it will use disk space, of course. But not much. And once you have the temp table populated, you can do something like this (also untested):
set nocount, xact_abort on;
declare @MinId int, @MaxId int;
select @MinId = min(RowNum), @MaxId = max(RowNum)
from #doc_numbers;
declare @Increment int = 1000;
declare @StartRange int = @MinId;
declare @EndRange int = @StartRange + @Increment;
declare @NumInserted int;
while @StartRange <=MaxId
begin
begin transaction;
insert target(col1, col2)
select t.col1, t.col2
from tbl t join #doc_numbers n on t.doc_number = n.doc_number
where n.RowNum >= @StartRange and n.RowNum < @EndRange
commit transaction;
set @StartRange = @EndRange;
set @EndRange = @StartRange + @Increment;
end
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.