none
INSERT INTO statement for multiple rows from a DataTable RRS feed

  • Question

  • It’s simple to insert one row at a time:

    INSERT INTO MyTable (field1, field2) VALUES (‘f1’, ‘f2’)

     

    But how would you insert all rows from a DataTable into a database table without looping through the rows. In other words, what’s the equivalent of this:

    INSERT INTO Store_Information (store_name, Sales, Date)

    SELECT store_name, Sales, Date

    FROM Sales_Information

     

    when the data is in a DataTable?

     

    Thanks

    Tuesday, January 15, 2008 4:02 PM

Answers

  • That is not possible yet. You must use insert into or some other way of inserting row by row. But in SQL 2008 you will be able to set your table as parameter to stored procedure and execute insert into .. select from  in the procedure.

    Tuesday, January 15, 2008 8:58 PM

All replies

  • you could do something like

     

    foreach (DataRow r in dtSource.Rows)

    {

    dtDest.ImportRow(r);

    }

     

    but isn't your insert into query a better approach? certainly faster and you must have the originating sql of the datatable?
    Tuesday, January 15, 2008 8:16 PM
  • That is not possible yet. You must use insert into or some other way of inserting row by row. But in SQL 2008 you will be able to set your table as parameter to stored procedure and execute insert into .. select from  in the procedure.

    Tuesday, January 15, 2008 8:58 PM
  • If you need to transfer data into SQL Server, you could do it in .NET 2.0 using SqlBulkCopy class. It allows transferring data from DataTable or some other data source into SQL Server using faster bulk operation.

     

    Wednesday, January 16, 2008 11:26 AM
    Moderator
  •  

    Just to clarify a bit the source of the data: The information is not available from a table but from a DataGridView. That’s because I use the DataGridView to filter and sort the data. Then I need to write it to an Excel file. So, I’m using the Excel file as a database and I was hopping I do not have to waste time iterating each row of data.

    But, now I understand what you said: that the only way possible is row by row. That’s life!
    Friday, January 18, 2008 8:14 PM
  • That is not the only way for Excel. If you do it using Jet, then yes you need to insert records one by one, but there are several third-party components on a market which export data into Excel without using any SQL statements and do it in a bulk, so it works faster. If speed is critical for you then you might consider using those components.

     

    Monday, January 21, 2008 11:15 AM
    Moderator