Inserting N Rows In a Loop Committing After Each Mth Row

Oracle to SQL Server Migration

It is often useful to test the performance of Oracle or SQL Server by inserting a huge number of rows with dummy data to a test table.

You can use the following PL/SQL script to insert 100,000 rows into a test table committing after each 10,000th row:

Oracle:

  DROP TABLE sales;
 
  -- Define a test table 
  CREATE TABLE sales
  (
     id NUMBER(10),
     created DATE
  );
 
  -- SQL*Plus command to enable DBMS_OUTPUT.PUT_LINE output
  SET SERVEROUTPUT ON
 
  -- Run a loop to insert 100,000 rows
  DECLARE i NUMBER(10) := 1;
                startd DATE := SYSDATE;
  BEGIN
    WHILE i <= 100000 LOOP
 
      INSERT INTO sales VALUES (i, SYSDATE);
      i := i + 1;
 
      -- Commit after each 10,000 row
      IF MOD(i, 10000) = 0 THEN
        COMMIT;
      END IF;
 
    END LOOP;
 
     -- Output the execution time in seconds 
    DBMS_OUTPUT.PUT_LINE((SYSDATE - startd)*24*60*60);
    END;
    /

The script outputs the number of seconds required to inserts all rows. Feel free to modify the number of total rows, commit count and table definitions to meet your requirements.

In SQL Server, you can use the following Transact-SQL script to insert 100,000 rows into a test table committing after each 10,000th row:

SQL Server:

  -- 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 <= 100000 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

The script also outputs the number of seconds required to inserts all rows.

• Oracle to SQL Server Migration Knowledge Base
• Migration Convenience Scripts

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - July 2012