none
INSERT BULK? RRS feed

Answers

  • INSERT BULK indicates a bulk insert operation using a client API.  This includes OLE DB IRowsetFastLoad (which is what SSIS uses with an OLE DB destination "fast load"), ODBC BCP calls and SqlClient SqlBulkCopy.

    See http://msdn.microsoft.com/en-us/library/ms141237.aspx for how to use fast load with an OLE DB Destination.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Naomi NModerator Wednesday, January 18, 2012 3:38 AM
    • Marked as answer by Gaurav 17 Wednesday, January 18, 2012 4:20 PM
    Wednesday, January 18, 2012 1:32 AM
  • Thanks Dan for the clarification. I am still not sure what is the difference between the normal insert and bulk insert? How it makes the difference in terms of perfromance?

    BULK INSERT or INSERT BULK are designed to load large amounts of data efficiently from an external source such as a file. Given the correct circumstances, the operation is minimally logged, which is a key point in making the´operation efficient.

    These days, things are getting blurred. Starting with SQL 2008, a regular INSERT statement can also be minimally logged given the right circumstances. However, an INSERT statement typically takes it input from within SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Gaurav 17 Wednesday, January 18, 2012 4:20 PM
    Wednesday, January 18, 2012 8:21 AM

All replies

  • INSERT BULK indicates a bulk insert operation using a client API.  This includes OLE DB IRowsetFastLoad (which is what SSIS uses with an OLE DB destination "fast load"), ODBC BCP calls and SqlClient SqlBulkCopy.

    See http://msdn.microsoft.com/en-us/library/ms141237.aspx for how to use fast load with an OLE DB Destination.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Naomi NModerator Wednesday, January 18, 2012 3:38 AM
    • Marked as answer by Gaurav 17 Wednesday, January 18, 2012 4:20 PM
    Wednesday, January 18, 2012 1:32 AM
  • You can use BULK INSERT from SSMS, check BOL for details:  http://msdn.microsoft.com/en-us/library/ms188365.aspx
    - Vishal

    SqlAndMe.com

    Wednesday, January 18, 2012 2:54 AM
  • Hi Vishal...I believe there is a difference between the "INSERT BULK" and "BULK INSERT". As Dan did mention it is basically this is used by client API. The SSIS Table Fast Load also calls the same.

    Thanks Dan for the clarification. I am still not sure what is the difference between the normal insert and bulk insert? How it makes the difference in terms of perfromance?


    Thanks, Gaurav
    http://gauravsqlserver.blogspot.com
    Wednesday, January 18, 2012 3:47 AM
  • Thanks Dan for the clarification. I am still not sure what is the difference between the normal insert and bulk insert? How it makes the difference in terms of perfromance?

    BULK INSERT or INSERT BULK are designed to load large amounts of data efficiently from an external source such as a file. Given the correct circumstances, the operation is minimally logged, which is a key point in making the´operation efficient.

    These days, things are getting blurred. Starting with SQL 2008, a regular INSERT statement can also be minimally logged given the right circumstances. However, an INSERT statement typically takes it input from within SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Gaurav 17 Wednesday, January 18, 2012 4:20 PM
    Wednesday, January 18, 2012 8:21 AM
  • Thanks Erland. Appreciate for helping on this.

     


    Thanks, Gaurav
    http://gauravsqlserver.blogspot.com
    Wednesday, January 18, 2012 4:20 PM