Ramesh Bathini's Blog

How could you insert 1 million records into a database table

Ramesh Bathini's photo
Ramesh Bathini
·

4 min read

How can you create 1 million records? By writing test case or procedure or function.

You can insert records from one table to another table

You can use the import wizard option from different tools

You can take the help of Microsoft excel to build the queries

If you more patience you can prepare the queries manually as well.

In order to get the desired output, you can achieve it anyway by taking time but how effectively and smartly completing the task in a short period of time is required for everyone.

Well, It is possible to create as many records as you want by executing simple SQL statements.

Well, it is indeed to create a large number of records sometimes, It might be for testing or developing something.

In this article, I will discuss SQL scripts to execute in Oracle as well as MS-SQL server databases and it is also possible to do the same for other databases but SQL syntax is different for it.

Here, I am taking Student as an example to insert 1 million records into the database table.

Student table contains 2 columns in that one of them having primary key constraint and in a loop iterating 1 million times to insert records into SQL server database.

Here is the script for the same:

     create table Student (
       id   VARCHAR(7),
       name VARCHAR(40),
       constraint ID_PK primary key (id)
      );
        declare @id int 
        select @id = 1
        while @id >=1 and @id <= 1000000
        begin
          insert into Student values(@id, 'jack' + convert(varchar(5), @id))
          select @id = @id + 1
        end

        select * from student;

1000Records.PNG

I am taking the same example to insert multiple records into the Oracle database table. Here is the SQL script for the same.

Oracle having a keyword called INSERT ALL which can be used to create any number of records.

Multiple rows are inserted into a table using the INSERT ALL statement and by using the inserting the results of the select query.

The syntax for it is: INSERT ALL INTO table_name (col1, col2, col3) VALUES ('val1','val2,'val3');

Here, the question here is how can we insert multiple records and how can handle if a column enabled with constraint

When having multiple rows

    INSERT ALL  
      INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
      INTO table_name(column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
      INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
    SELECT * FROM dual;

Well, We just need to use ROW_NUMBER() function to get a unique number

Happy programming!

SQLSQL ServerOracleJavaMySQL