CODEWITHSUNDEEP
sqlsql-servert-sqlbatch-processing
shyam
shyam

Reputation: 11

Batch insert your while loop

I needed help on the following query. How can I insert all the rows from table 1 to table 2 in batches of 10 using a while loop (Table 1 and Table 2 are identical tables and Table 1 has 500 records). I want to avoid using temporary tables.

I am trying to load in batches similar to following:

Declare @rowcount int

while @rowcount > 0
begin
  Insert into table2(id, name, address)
    Select top 10 id,name, address from table 1)

  set @rowcount = @rowcount +1
end

Upvotes: 0

Views: 9916

Answers (4)

KumarHarsh
KumarHarsh

Reputation: 5094

Thing are confusing from your end.

when it is only 500 records then why do you want to Batch Insert and use While Loop. Batch Insert is suitable for millions of records.

you have to tell what is Unique Key.In my script I have assume ID.

declare @Batch int=12
declare @PIndex int=1
Declare @TotalRecord int=100
Select @TotalRecord=count(id) from table1
declare @PageNo int=case when @TotalRecord%@Batch=0 then @TotalRecord/@Batch else (@TotalRecord/@Batch)+1 end
--select @PageNo

While (@PIndex<=@PageNo)

begin

insert into table2
select * from table1 t1
where not exists(select 1 from table2 t2 where t1.id=t2.id)
ORDER BY t1.id OFFSET @Batch * (@PIndex - 1) ROWS
FETCH NEXT @Batch ROWS ONLY;

set @PIndex=@PIndex+1

end

where not exists(select 1 from table2 t2 where t1.id=t2.id) is for precaution measure as we don't know table design.

Code is not tested.

Upvotes: 0

cte6
cte6

Reputation: 637

An option is to create a temp table with a status column to indicate those unprocessed and then use that to count unprocessed and indicate if there is a new batch to process.

Declare @rowcount int = 1

Select *, cast(0 as int) as status
into #tmpToProcess
from table1

while @rowcount > 0
begin
  begin transaction

  update top(10) #tmpToProcess
  set status = 1 --inProcess

  Insert into table2(id, name, address)
  Select top 10 id,name, address from #tmpToProcess where status = 1

  update #tmpToProcess
  set status = 1 --inProcess
  where status = 2 --Processed

  commit transaction

  select @rowcount  = count(*)
  from #tmpToProcess 
  where status = 0
end

I know it is a little long but this way you can process in groups of 10 records and keep track exactly which have been processed and how many are pending. Just may be add a try catch to round it up.

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

if your id is your primary key, you can consider this query.

And by using row_number() will make sure your will not duplicate your records based your unique key

where t2.rn between (@rowcount * 10) + 1  and (@rowcount*10) + 10

Here's your complete script

Declare @rowcount int
set @rowcount = 0
while @rowcount > 0
begin
    Insert into table2(
        id,
        name,
        address)
    select t1.id, t1.name, t1.address 
    from table1
    inner join
        (Select row_number() over (order by id) as rn, id
        from table1) t2 on t2.id = t1.id
    where t2.rn between (@rowcount * 10) + 1  and (@rowcount*10) + 10

    set @rowcount = @rowcount + 1
end

Upvotes: 0

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

if you wanna do this over SSMS as a one-off then do below

INSERT INTO table2
  SELECT TOP 10
    *
  FROM table1 t1
  WHERE NOT EXISTS (SELECT
    1
  FROM table2 t2
  WHERE t2.id = t1.id)

GO 10  -- will loop for 10 times

If over a stored script then remove GO 10 and wrap the insert query with your while loop

Upvotes: 0

Related Questions