Loop insert SQL query

Question

I have the below query which returns 400 million rows. I want to run the query so it loops through and inserts 1 million records at a time. Please can I get the loop query.

insert into AST (DataAreaId, Name)

select f.DataAreaId, its.Name....... etc
from Transform.InventFin f
inner join Staging.INVENTSETTLEMENT its
on f.ITSRECID=its.RECID
and f.DataAreaId=its.DATAAREAID

PSK · Accepted Answer · 2018-03-12 10:48:07Z

1

Try like following(Assuming that DataAreaId is Unique, if not you need to include those columns in NOT EXISTS).

declare @Count int
set @Count = 1
while @Count > 0
   begin
         insert into AST (DataAreaId, Name)
        select TOP (1000000)  f.DataAreaId, its.Name....... etc
        from Transform.InventFin f
        inner join Staging.INVENTSETTLEMENT its
        on f.ITSRECID=its.RECID
        and f.DataAreaId=its.DATAAREAID 
        WHERE NOT EXISTS
        (
         SELECT 1 FROM AST A WHERE AST.DataAreaId = F.DataAreaId
        )
     set @Count = @@ROWCOUNT
   end

answered Mar 12, 2018 at 10:48

PSK

18k5 gold badges35 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3266033 Over a year ago

DataAreaID isn't the primary key so will this still apply?

PSK Over a year ago

You need to make it unique to avoid any duplicates, for this you will be required to add the condition to your to WHERE AST.DataAreaId = F.DataAreaId and AST.Col1= tbl.Col1 AND AST.Col2=tbl.Col2. like this

Collectives™ on Stack Overflow

Loop insert SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related