Bulk inserts

Bulk inserts

Post by Johan Torssel » Wed, 10 Jan 2001 18:53:40



Is it possible to issue bulk inserts to SQL server via the .Net Framework
classes?
Compare with OLE DB:s IRowsetFastLoad and DB-Librarys bcp_sendrow,
bcp_batch.
 
 
 

Bulk inserts

Post by Malcolm Stewa » Thu, 11 Jan 2001 06:59:26


Johan,

<<
Is it possible to issue bulk inserts to SQL server via the .Net Framework
classes?
Compare with OLE DB:s IRowsetFastLoad and DB-Librarys bcp_sendrow,
bcp_batch.

No. ADO.NET is not designed for non-logged bulk operations. You've pretty
much got similar restrictions to what you can achieve via ADO or RDO,
though the SQL Managed Provider does appear to be about 25% faster than the
ADO Managed Provider. You can batch a number of INSERT statements into one
unit of execution, but the operation will still be logged.

Malcolm Stewart
Microsoft Beta Technical Support

 
 
 

Bulk inserts

Post by Al Manin » Sun, 14 Jan 2001 04:56:39


I use IRowsetFastload and was wondering the same thing.  It is a logged
transaction.  It builds a memory map and sends the data all at once.  When
you batch a number of insert statement in the new ADO - is it then treated
as single inserts when it hits the sql server?

--Al


Quote:> Johan,

> <<
> Is it possible to issue bulk inserts to SQL server via the .Net Framework
> classes?
> Compare with OLE DB:s IRowsetFastLoad and DB-Librarys bcp_sendrow,
> bcp_batch.

> No. ADO.NET is not designed for non-logged bulk operations. You've pretty
> much got similar restrictions to what you can achieve via ADO or RDO,
> though the SQL Managed Provider does appear to be about 25% faster than
the
> ADO Managed Provider. You can batch a number of INSERT statements into one
> unit of execution, but the operation will still be logged.

> Malcolm Stewart
> Microsoft Beta Technical Support

 
 
 

Bulk inserts

Post by Johan Torssel » Tue, 16 Jan 2001 07:44:51


I know that bulk copy is usually refered to as a non-logged operation.

Reading BULK INSERT (T-SQL) in Transact-SQL Help, it states that each batch
is copied to the server as
one transaction. Further it states that a table can be loaded concurrently
by multiple clients if ROWS_PER_BATCH
and TABLOCK is specified. It also says that an entire file can be sent to
the server as a single transaction.
When using IRowsetFastLoad ROWS_PER_BATCH, TABLOCK can be set via property
identifier
SSPROP_FASTLOADOPTIONS in IDBProperties.
When I use SQL Server 7 Profiler to logg a test program that uses
IRowsetFastLoad as above, transactions are used.

Would the above not imply that bulk insert is/can be a logged operation?

Apart from this reasoning I would still like to see it possible to do bulk
inserts via ADO. Could it not be possible
to set a Provider specific property that makes the SQL Server Provider use
IRowsetFastLoad?

Johan Torssell


Quote:> Johan,

> <<
> Is it possible to issue bulk inserts to SQL server via the .Net Framework
> classes?
> Compare with OLE DB:s IRowsetFastLoad and DB-Librarys bcp_sendrow,
> bcp_batch.

> No. ADO.NET is not designed for non-logged bulk operations. You've pretty
> much got similar restrictions to what you can achieve via ADO or RDO,
> though the SQL Managed Provider does appear to be about 25% faster than
the
> ADO Managed Provider. You can batch a number of INSERT statements into one
> unit of execution, but the operation will still be logged.

> Malcolm Stewart
> Microsoft Beta Technical Support

 
 
 

Bulk inserts

Post by Al Manin » Sat, 20 Jan 2001 01:39:03


Amen.  If SQL Server has the ability to do it - ADO should provide an
interface for it.

--Al

> I know that bulk copy is usually refered to as a non-logged operation.

> Reading BULK INSERT (T-SQL) in Transact-SQL Help, it states that each
batch
> is copied to the server as
> one transaction. Further it states that a table can be loaded concurrently
> by multiple clients if ROWS_PER_BATCH
> and TABLOCK is specified. It also says that an entire file can be sent to
> the server as a single transaction.
> When using IRowsetFastLoad ROWS_PER_BATCH, TABLOCK can be set via property
> identifier
> SSPROP_FASTLOADOPTIONS in IDBProperties.
> When I use SQL Server 7 Profiler to logg a test program that uses
> IRowsetFastLoad as above, transactions are used.

> Would the above not imply that bulk insert is/can be a logged operation?

> Apart from this reasoning I would still like to see it possible to do bulk
> inserts via ADO. Could it not be possible
> to set a Provider specific property that makes the SQL Server Provider use
> IRowsetFastLoad?

> Johan Torssell



> > Johan,

> > <<
> > Is it possible to issue bulk inserts to SQL server via the .Net
Framework
> > classes?
> > Compare with OLE DB:s IRowsetFastLoad and DB-Librarys bcp_sendrow,
> > bcp_batch.

> > No. ADO.NET is not designed for non-logged bulk operations. You've
pretty
> > much got similar restrictions to what you can achieve via ADO or RDO,
> > though the SQL Managed Provider does appear to be about 25% faster than
> the
> > ADO Managed Provider. You can batch a number of INSERT statements into
one
> > unit of execution, but the operation will still be logged.

> > Malcolm Stewart
> > Microsoft Beta Technical Support

 
 
 

1. Bulk Insert / Stored Proc / DataSet / ADO.NET

I am new to ADO.Net and I am looking to insert a large quantity of data (i.e. 1000's of records)
into a SQL Server database.  

The data is currently stored in a DataSet and a Stored Proc exists for the insert.  I don't have to
keep the data in a DataSet if it is easier to do it another way.

There is a VERY good possibility with the data in the DataSet, that a duplicate will exist in the
Database and the insert should not succeed for this individual record.

An example or pseudocode would greatly be appreciated.

Thanks,
Dave

2. WinNT as router for small network

3. Fast Bulk INSERT

4. putting an algorithm in a doc

5. Bulk Inserts

6. "Just" company light boxes?

7. using writexml for bulk insert

8. QWK Offline Readers.

9. Bulk Insert

10. Bulk insert in OracleClient?

11. SQL Server 2000 - Bulk insert changes row order

12. Bulk Insert JSON data into SQL Server

13. bulk insert how to specify text wrapper