homepage Welcome to WebmasterWorld Guest from 54.161.240.10
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
PHP/MSSQL Massive data insert
What would be the best approach?
milocold




msg:3603419
 10:05 pm on Mar 17, 2008 (gmt 0)

Hi fellas,

I'm trying to find the most efficient method to use in order to insert 5 to 10 thousand records. Basically, I have a script that creates pins; the user just selects the number of pins to create.

I have to believe that something better than this exists:

#open DB

#while loop to perform a crazy amount of mssql inserts

#close DB

Anybody have any ideas or creative solutions? =)

Currently, I'm doing research to find out if I can dynamically create an excel file (containing all the pins) and push that data into the mssql DB via php. I don't think it's possible though...=(

Thank you in advance!

M.Cold

 

Steerpike




msg:3603821
 10:14 am on Mar 18, 2008 (gmt 0)

That sounds, ummm, wildly inefficient. Can I ask exactly what you're trying to do? and what your database/tables are trying to store? Maybe there's a better way of organising the tables so that you don't need to do so many inserts.

milocold




msg:3603832
 10:38 am on Mar 18, 2008 (gmt 0)

I do agree that the method I posted sucks, but it's the only thing I've found in relation to performing bulk inserts from dynamically generated data.

I don't think that I have access on the DB server to create stored procedures, but if I do that will be the route I'll be taking. Even though, at the end of the day, the same while-loop methodology will be applied...I think. =/

Oh, and the data being stored (in a single table) are the combination of consecutive numbers, random numbers, and constants.

Thanks,

M. Cold

penders




msg:3603847
 11:02 am on Mar 18, 2008 (gmt 0)

I've been away from the DB scene for a while, but we used to be able to do bulk inserts from a text file in Sybase SQL. Essentially one INSERT statement that read the contents of a file (CSV, TAB seperated etc.) Without the overhead of multiple INSERTS and continuous index building.

A bit of a google... does your version of MSSQL support this sort of thing? (Is there a 'bcp' - bulk copy program?)

BULK INSERT tblMyTable FROM 'c:\mydata.txt' WITH (FIELDTERMINATOR = ',')

May be the database forum [webmasterworld.com] can offer more help...?

milocold




msg:3604260
 5:20 pm on Mar 18, 2008 (gmt 0)

Thank you Penders!

I think your right on the money. I was thinking DTS package or bulk insert (from excel to db), but a CSV would be much better.

*hits himself on the head*

Plus, I'm not even sure you can work with DTS packages from PHP. I know you can using ASP/.Net platform. At any rate, thank you again. I'm gonna go perform the test now! =)

M. Cold

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved