sqlblue (1/5/2009)

---

Can I do batch insert or update with a set of rows at a time(does not have to be in order of any kind), and have it automatically go to the next set of rows that have not been inserted or updated? If so, how can I do it?
thanks for any help.

Below is my script. The insert is for several millions records, and so I
break it down by filenumber but it is still big for insert,
so I try to do it in a small set.

insert into table2 (col1, col2, col3)
select table1.col1, table1.col2, table3.col3
from table1 inner join table3
on table1.col1 = table3.col1
where table1.filenumber = 1
go

insert into table2 (col1, col2, col3)
select table1.col1, table1.col2, table3.col3
from table1 inner join table3
on table1.col1 = table3.col1
where table1.filenumber = 2
go

sqlblue (1/5/2009)

---

Thanks Noel, but I wanted to be able to do just a set of row, say 10,000 or
100,000 at a time. How do i do it so that the query will continue to the next
10K or 100k records, without using cursor. if i do rowcount or top of 10,0000, it would only running once for only those records that set, right?

sqlblue (1/5/2009)

---

Thanks Noel, but I wanted to be able to do just a set of row, say 10,000 or
100,000 at a time. How do i do it so that the query will continue to the next
10K or 100k records, without using cursor. if i do rowcount or top of 10,0000, it would only running once for only those records that set, right?

SELECT IDENTITY (INT, 1, 1) AS RowID, Table1PK
INTO #BatchControl 
FROM table1 
INNER JOIN table3 
	ON table1.col1 = table3.col1

DECLARE @Batchsize
SET @Batchsize = 10000

-- WHILE there are rows left 
INSERT INTO table2 (col1, col2, col3)
SELECT table1.col1, table1.col2, table3.col3
FROM table1 
INNER JOIN table3 
	ON table1.col1 = table3.col1
INNER JOIN (SELECT TOP (@Batchsize) Table1PK FROM #BatchControl ORDER BY RowID) b ON b.Table1PK = table1.Table1PK

DELETE #BatchControl
FROM (SELECT TOP (@Batchsize) RowID FROM #BatchControl ORDER BY RowID) b WHERE b.RowID = #BatchControl.RowID
-- END

sqlblue (1/5/2009)

---

Can I do batch insert or update with a set of rows at a time(does not have to be in order of any kind), and have it automatically go to the next set of rows that have not been inserted or updated? If so, how can I do it?
thanks for any help.

Below is my script. The insert is for several millions records, and so I
break it down by filenumber but it is still big for insert,
so I try to do it in a small set.

insert into table2 (col1, col2, col3)
select table1.col1, table1.col2, table3.col3
from table1 inner join table3
on table1.col1 = table3.col1
where table1.filenumber = 1
go

insert into table2 (col1, col2, col3)
select table1.col1, table1.col2, table3.col3
from table1 inner join table3
on table1.col1 = table3.col1
where table1.filenumber = 2
go