Hi,

I have three different tables, two in DBF (for getting the data)and another in SQL (for writing the new table)
====
sql="SELECT * FROM M_SALSIN"
rs.open sql, conn
DO WHILE NOT RS.EOF
     sql3="SELECT * FROM MAE_CLI WHERE COD_CLI="&TRIM((rs("cod_cli")))&""
    rs3.open sql3,conn
    IF rs3.EOF THEN
    ELSE
    sql5="INSERT INTO CINTAS_PDA_SIN_CARGO_MAIN(cod_alb,cod_cli,fecha,te rminado) VALUES('"&rs("cod_alb")&"','"&rs("cod_cli")&"','29/11/2006','0')"
    rs5.open sql5, conn5
    END IF
    rs3.close
    rS.MOVENEXT
LOOP
===
First I open a DB with the new data.
Then I enter the loop, where I search the table of the names, so that I can INSERT the data of the first SQL, with the name of the second SQL statement.

I noticed that this is just taking to long. As everytime it has to open the DB connection....
Does anybody can give me some tips on doing this better, so that it might take less time to do the process.

The idea is that it runs at 3 a.m. to prepare the new table for the next morning.

Many thx for any idea.

Johny

If this was all in MSSQL I could do this with 1 query.

INSERT INTO CINTAS_PDA_SIN_CARGO_MAIN(cod_alb,cod_cli,fecha,te rminado)
SELECT cod_alb, cod_cli, '29/11/2006', '0' FROM MAE_CLI WHERE COD_CLI IN (SELECT cod_cli from M_SALSIN)

The WHERE Clause could also just be COD_CLI = (SELECT DISTINCT cod_cli from M_SALSIN) if there is only 1 cod_cli in the table.

If you can use the above query,you will only have to make 1 connection to your database and it should run 100 times faster then what you are tying to do.


-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

Thx dparsons,

I will give it a shot. I was already looking in that direction. As you statet, it must speed up the search...

Thx again

Johny

Write it as a Stored Procedure in SQL and then set it to be a job in the SQL Server Agent, timed to run daily at 3am.

Regards,

Sean Anderson