none
Best way to insert multiple records RRS feed

  • Question

  •  

    Hi

     

    Just trying to learn ADO.net.  Using VB2005 and SQL Server 2005.

     

    Say I have an array with 200 elements.  And I want to insert these array elements into a table in SQL server.

     

    Is the best way to use a Command Object and loop it?

     

    So something like:

    Code Block

    Dim command as new sqlcommand(SQL, Connection)

    For x = 0 to Ubound(MyArray)

    Change Values for SQL insert

    command.ExecuteNonQuery()

    Next

     

     

    Or is there a 'better' way.  I'm not pulling anything from the DB, purely inserting new records.

     

    Cheers

     

     

    Friday, January 4, 2008 1:47 AM

Answers

  • There is no way to perform a direct bulk copy from an array to SQL Server. You can use the method you mentioned or export the data to another format, such as XML (mentioned by the other poster) or another data source such as a text file.

     

    The SQLBulkCopy class can be used with other data sources to perform a batch insert for SQL Server.

     

    Friday, January 4, 2008 2:43 PM

All replies

  • Hi,

     

    I think, it will be a good idea to avoid that many hits to SQL Server. With single access to the database, records that need to be inserted must be passed as XML string and leave SQL Server in parsing the XML string and insert records into the table. This will be more efficient way to insert multiple records in SQL Server.

     

    Mallik

    Friday, January 4, 2008 7:54 AM
  • There is no way to perform a direct bulk copy from an array to SQL Server. You can use the method you mentioned or export the data to another format, such as XML (mentioned by the other poster) or another data source such as a text file.

     

    The SQLBulkCopy class can be used with other data sources to perform a batch insert for SQL Server.

     

    Friday, January 4, 2008 2:43 PM