asked on

Adding SQL record under delphi

Hi, I have some data to import into my SQL database, I'm currently doing speed tests and I can see that it takes 5 minutes to add 30000 records using either adodataset (append) or adocommand (insert into) which is pretty lame considering that a DBF file does this kind of operation in 4 seconds.

I noticed that when importing from a CSV file from sql manager it takes 1 second for the same amount of records. Is there any way for me to achieve this kind of performance using queries with the ADO components in Delphi?

ASKER CERTIFIED SOLUTION

Sinisa Vuk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Buropro-Citation

ASKER

I have tried the set of insert statement, takes 5 minutes also to execute. I have read about the bulk insert, problem with this is that I have to create a TXT file and hope that it will be inserted the right way depending on the field type (date, float etc..), no automatic conversion of data like excel tends to to sometime when importing text file. This is a nice solution but it is the same as importing the CSV from the SQL manager, I'm looking for a solution that would not involve going through a text file.

Buropro-Citation

ASKER

Another problem with the text file solution is that I have to make sure that the field separation caracter is not in any of my data. Also, I'm not sure if it would work well with blob field, not to mention that this would make a huge file and I think that text file have some problem when it's too big.

SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Buropro-Citation

ASKER

Though every solutions mentioned here were good, I have found the BEGIN TRANS and COMMIT TRANS function to solve all my problems. It is pretty much as fast as importing from a CSV. Not as fast as one million rows per seconds but fast enough for my needs, I'll buy SDAC if I wan't any further speed.