How to insert 10000 value in database table

Question

How to insert 10000 value in DB table using For or Foreach loop in MVC.

Piyush Pansuriya · Accepted Answer

Hi,

If you are working with List, For loop is better than Foreach loop.

OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
for (int i = 0; dt.Rows.Count > i; i++)
{
command = new SqlCommand("INSERT INTO TableName (Column1,Column2)
VALUES('"+dt.Rows[i][0].ToString()+"','"+dt.Rows[i][1].ToString()+"')",con);
command.ExecuteNonQuery();
}
con.Close();

Nitin Sontakke · Answer

You can even insert record in the batch of 1000 (or less) with the help of following syntax:  insert   into  [dbo].[tblTableName] ([column1], [column2])  values     ( 'value11' ,  'value12' )   ,( 'value21' ,  'value22' )   ,( 'value31' ,  'value32' )   ,( 'value41' ,  'value42' )   All you have to do is construct this string in a loop and execute. Remember that 1000 records are inserted as a single statement and as a single transaction.

Nikunj Satasiya · Answer

Hello Rajesh, You can Insert 100000 rows simultaneously. In Sql Server to insert bulk record you can use following Sql Script : -- Drop the test table if it exists    IF OBJECT_ID( 'sales' ,  'U' )  IS   NOT   NULL          DROP   TABLE  sales ;          -- Table definition    CREATE   TABLE  sales       (         id  INT  ,         created DATETIME       ) ;     GO        SET  NOCOUNT  ON           -- Run a loop to insert 100,000 rows    DECLARE  @i  INT  = 1 ,       @start DATETIME = GETDATE() ;       WHILE @i       BEGIN             -- Start a transaction            IF @i % 10000 = 1                 BEGIN   TRANSACTION  ;                INSERT    INTO  sales            VALUES   ( @i, GETDATE() ) ;            SET  @i = @i + 1 ;            -- Commit after each 10,000 row            IF @i % 10000 = 0                 COMMIT  ;            END           -- Output the execution time in seconds     SELECT   DATEDIFF(ss, @start, GETDATE()) ;      GO