You can make use of OPENROWSET for that
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database={excel file path}', 'SELECT * FROM [Sheetname$]')
SELECT required columns....
FROM
(
SELECT *,ROWNUMBER() OVER (ORDER BY PK) AS Rn
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database={excel file path}', 'SELECT * FROM [Sheetname$]')
)t
WHERE Rn <=100
..
PK will be key column and you need to put appropriate excel paths and also sheet name.
for each sheets you need to set where condition appropriately to select 100 row batch
If you want to automate it you need to have some kind of looping logic and also use dynamic sql to generate sheet names
commented on Jan 18 2012 12:25AM