SQL Server - any method to fast insert in sql table

Asked By anbu n on 24-May-11 03:39 AM
any method to fast insert in sql table, like while inserting more rows eg like more than 40  it takes around 30 secs & more

i am collecting all the rows in a list object , then i do for loop to insert each record..
Riley K replied to anbu n on 24-May-11 03:59 AM
I dont think there is  a way that executes faster tha SQL query unless you have defined them properly with proper indexes,
if there are joins in you query it would sure take such time.

It depends on how optimize you write your query.
TSN ... replied to anbu n on 24-May-11 04:12 AM
hi..
It depends on the solution you are making if you are using linq to sql here is the solution...

Consider your List: IList<Customer> foo;

Using LINQ To SQL

Create a LINQ To SQL datacontext, and it's something like this:

using (var db = new CustomerDataContext())
{
   db.Customer.InsertAllOnSubmit(foo);
   db.SubmitChanges();
}

if you are using normal queries just write the stored procedure it would be a nice option

Using Stored Procedures

using (var conn = new SqlConnection())
{        
     using (var cmd = new SqlCommand(conn))
     {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "InsertCustomer";

        foreach (Customer c in foo)
        {
             cmd.Parameters.Clear();
             cmd.Parameters.Add(new SqlParameter("@CustomerName", c.Name);

             conn.Open(dbConnString);
             cmd.ExecuteNonQuery();
             conn.Close();
        }
    }
}
and thidly you can also Bulk insert which is used to insert multiple data at once...
Ravi S replied to anbu n on 24-May-11 04:18 AM
HI

My suggestion is to use Bulk Insert

here is the syntax

BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
  FROM 'data_file' 
   [ WITH 
  ( 
   [ [ , ] BATCHSIZE = batch_size ] 
   [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ [ , ] DATAFILETYPE = 
  { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
  )]
refer the link for examples
http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file
http://www.roseindia.net/sql/sql-bulk-insert-example.shtml