what is the best way insert millions of records?

Question

what is the best way insert millions of records?

SQL*

SSCertifiable

Points: 6638
More actions
April 6, 2010 at 6:43 am

#142434

hi,
what is the best way to insert millions of records how can i optimize the insertion, and how can we do optimize of a particular database.
thanks,
🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GregoryF SSCertifiable Points: 6296 More actions · Answer 1

There is no way to answer that given the information that you provided. It would be helpful to know what is being inserted, the size of the existing table, the indexes, and the hardware.

Without anymore information, I would suggest off-hours.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

1. Use BULK INSERT method

2. Uss batch approach in DML operations(let says work on 10,0000 records one by one)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 3

Chandra Sekhara Vyas Dhara

SSChampion

Points: 10207

April 6, 2010 at 7:29 am

#1146479

Use BCP. It will faster and not logged

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

yes, as vyas pointed out, use BCP.. very fast utility to bulk insert millions of rows..

Paul White SSC Guru Points: 150467 More actions · Answer 5

bcp and BULK INSERT are not the only methods, and are not necessarily minimally-logged.

For full, and accurate, details see:

Importing and Exporting Bulk Data (Books Online link)

Be sure to explore the related links and sub-sections from that page, to fully understand the issues.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi