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 · 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