CODEWITHSUNDEEP
phpsqlsql-serversql-server-2008
Kasun Rajapaksha
Kasun Rajapaksha

Reputation: 536

How to insert more than 10000 rows to MSSQL Table

I have a PHP project where I have to insert more than 10,000 rows to a SQL Table. These data are taken from a table and checked for some simple conditions and inserted to the second table at the end of every month.

How should I do this.

I think need more clarification. I currently use small batch (250 inserts) transferring using PHP cronjob and it works fine. But i need to do this is most appropriate method.

What will be the most appropriate one.

  1. Cronjob with PHP as I currently use
  2. Exporting to a file and BULK import method
  3. Some sort of Stored procedure to transfer directly

or any other.

Upvotes: 1

Views: 5329

Answers (5)

Netlog
Netlog

Reputation: 135

it's so simple , you can do it using multiple while, since 10000 rows is not huge data!

$query1 = mssql_query("select top 10000 * from tblSource");
while ($sourcerow = mssql_fetch_object($query1)){
      mssql_query("insert into tblTarget (field1,field2,fieldn) values ($sourcerow->field1,$sourcerow->field2,$sourcerow->fieldn)");
}

this should be work as fine

Upvotes: 1

Anvesh
Anvesh

Reputation: 7703

SQL Server does not insert more than 1000 records in a single batch. You have to create separate batch for insertion. Here I am suggesting some of alternative which will help you.

Create one stored procedure. create two temporary table one for valid data and other for invalid data. one by one check all your rules and validation and base on that insert data into this both table.

If data is valid then insert into valid temp table else insert into invalid temp table.

Now, next using merge statement you can insert all that data into your source table as per your requirements.

you can transfer N number of records between tables so I hope this would be fine for you

Thanks.

Upvotes: 1

sectus
sectus

Reputation: 15464

Use insert SQL statement. :^ )

Adds one or more rows to a table or a view in SQL Server 2012. For examples, see Examples.

Example of using mssql_* extension.

$server = 'KALLESPC\SQLEXPRESS';
$link = mssql_connect($server, 'sa', 'phpfi');
mssql_query("INSERT INTO STUFF(id, value) VALUES ('".intval($id)."','".intval($value)."')");

Upvotes: 3

Freelancer
Freelancer

Reputation: 9074

Since the data is large, make the batch of 500 records for processing.

Check the condition for those 500 batches , till that time, make ready another batch of 500 and insert first batch and process so on.

This will not give load on your sql server.

By this way i daily process 40k Records.

Upvotes: 2

Sooraj Chandu
Sooraj Chandu

Reputation: 1310

Use BULK INSERT - it is designed for exactly what you are asking and significantly increases the speed of inserts.

Also, (just in case you really do have no indexes) you may also want to consider adding an indexes - some indexes (most an index one on the primary key) may improve the performance of inserts.

The actual rate at which you should be able to insert records will depend on the exact data, the table structure and also on the hardware / configuration of the SQL server itself, so I can't really give you any numbers.

Upvotes: 2

Related Questions