asked on

Writing data from SQL Database

I currently have code that reads data from an Sql Database, and inserts the data (records) into a delphi database. The code takes too long as it takes about 2 seconds for each entry, so when there are a lot of entries it takes too long. I would just like to know if someone could give me some code that does the same thing, but which would take a lot quicker. I have code that writes to the Sql database, and transfereing 2000 records takes about 6 minutes, which is fine but returning the same data, takes an hour.
Here is the code for writing the records as well as retieving them.

This is the code that writes to the Sql Database:
WriteEntry = "INSERT INTO tblEliteTransaction" _
& " (BatchNo,TransLineNo,BankBranchCode,BankAcctNumber,Amount,BankAcctHolderName,UserReference,ErrorCode) VALUES " _
& "('" & WEBatchNumber & "','" & WEEntryNumber & "','" & WEBranchCode & "','" & WEAccountNumber & "','" & WEAmount & "'" _
& ",'" & WEAccountHolderName & "','" & WEUserReference & "','" & 0 & "');"
'On Error GoTo ehEntry:
SQLDB.Execute WriteEntry

RsReadEntry.MoveNext

This is the code to retrieve the records, which i would like made faster:
WriteEntry = "INSERT INTO Entry_de" _
& "([Batch Number],[Entry Number],[Branch Code],[Account Number],Amount,[Account Holder's Name],[Transaction Type],[User Reference]) VALUES " _
& "('" & WEBatchNo & "','" & WETransLineNo & "','" & WEBankBranchCode & "','" & WEBankAcctNumber & "','" & WEAmount & "'" _
& ",'" & WEBankAcctHolderName & "','" & "CR" & "','" & WEUserReference & "');"
'On Error GoTo ehEntry:
ELITEDB.Execute WriteEntry

RsTransTransaction.MoveNext

If someone could help please.
Thanks!

ASKER CERTIFIED SOLUTION

wqw

membership

This solution is only available to members.

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

Start Free Trial

mscala

ASKER

Yes i am using ADO.

I used your code, but when you say rs.addnew Array(... ) on this line of code, it gives the following error message - The operation requested by the operator is not supported by the provider. Somewhere shouldn't we say where the data must be put, as in which table the data must be inserted into?

mscala

ASKER

Sorry the error message was - The operation requested by the application, is not supported by the provider.

prozak

Have you tried doing a commit after every 50 records or so?

mscala

ASKER

Sorry the error message was - The operation requested by the application, is not supported by the provider.

wqw

you've opened the recordset with "select ... from <TableName>", haven't you? try optimistic locking this way:

rs.Open "select..", ConnStr, adOpenKeyset, adLockOptimistic

</wqw>

Roopesh_Mohan

As i dont understand the way you populate RsTransTransaction.

I am just giving you a example

WriteEntry = "INSERT INTO tblEliteTransaction" _
& " (BatchNo,TransLineNo,BankBranchCode,BankAcctNumber,Amount,BankAcctHolderName,UserReference,ErrorCode)

SELECT (BatchNo,TransLineNo,BankBranchCode,BankAcctNumber,Amount,BankAcctHolderName,UserReference,ErrorCode)
FROM RsTransTransaction

I am sorry for not applying string properly...

now if you run

SQLDB.Execute WriteEntry

I hope to be faster... Post me on the updates....

-Roopesh

Brendt Hess

If you are using SQL Server 7.0 (the only version I've used, so the only one I can speak with any authority about), you might try using DTS to transfer the data.

First, try making a package to do the transfer in the Data Import and Export wizard. Execute it, and see how fast this works.

If this is significantly faster (as I have found that it usually is), then your program can execute the DTS package to perform the transfer. Examples of how to do this are discussed in the SQL Server Books Online topic "DTS Samples". Sample code may have to be retrieved from your SQL Server CD, as the samples are not installed by default.

If you're not using SQL Server 7.0, please ignore the above information :)

mscala

ASKER

wqw,

I am using ODBC, can this still be done using ODBC, as in using the array? I was mixed up with something else using ADO.

mscala

ASKER

bhess1,

HI, can you give me some sample code for the DTS, i am not familiar with this, and yes i am working with SQL7, but do not have the disks with the sample code on it. If you could please send me some sample that will do what i am trying to achieve here, which is basically reading information from a table on SQL, and transfereing all the data to a table on my computer.

This would be much appreciated.
Thanks!

Brendt Hess

The examples that I have are too large for posting here. Send me your E-Mail address to brendthess@yahoo.com and I will forward you some VB5 code examples.

andyclap

Have you tried wrapping the whole thing in a transaction? Some database engines (Access included) will do these inserts faster.
I'm not sure about the delphi engine - it may be it's just slow at inserting. If you're creating the table from scratch, try removing the indexes and adding them again at the end.