Linked Server

Hi,

Is there a way to batch INSERTs from a linked server?

For example, I'm importing data into a sql db using the following linked server query.

SELECT * INTO SQLDB.dbo.Table_SS FROM OPENQUERY (ORACLELINK, 'SELECT * FROM SCHEMA.Table_Ora')

If the table Table_Ora has say 5 million rows..........is there anyway that we can batch the INSERTs so that the query will commit at say every 30 or 40k rows?

or any other better approaches?

Thanks,
Siva.

There's a way for doing so in SQL, but you will not like it since it tends to unnecessary long times of execution. Much more suitable approach for this is a DTS / SSIS package (depending on version of MSSQL you are using) with Data Flow Task - in its settings you can specify the size, in rows, of such a batch.

Also, if this task was the only reason to create a linked server, you may easily get rid of it since DTS connects directly to the source server without any go-betweens.