Re: Batch Updates

I figured out in part what is wrong with my sql insert statement! I am using
an ADO connection for my ADOQuery that points to a text driver using a
schema.ini file and this is why this connection is not working with my
Access table for the insert to part of the sql statement. I think I need to
add something like this statement to get it to work but I am unsure of the
syntax.

INSERT INTO Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data\Delphi\Qbit\CubiScan.mdb
FROM delimited_file.txt

Can anyone verify the syntax for the sql statement on how to insert into an
access table reading from an connection that is built using the text driver?


"Brian Bushay TeamB" <BBushay@xxxxxxxxx> wrote in message
news:6d94125sla4gdqn02o4h3d8oisq93kl4ki@xxxxxxxxxx

Trying to perform some type of batch update using ADO to an Access
database.
Is there a way similiar to the BDE batch move object that can be used in
ADO?
No

I am currently loading a delimited file into a query and then parsing the
query row by row and using either an insert or edit depending on whether
the
key field already exists to update my ADO table but this process is very
slow. It takes 4 minutes to do this process using ADO and only 10 seconds
to
use the BDE batch move and load it into a paradox table. (apx 65000
records)

Any help here would be much appreicated!
I suggest using an Update query followed by an Insert query

with an update looking like this

update table2 t
inner Join table2a t2
on t.id = t2.id
set t.name = t2.name

and an insert like this

insert into table2
(id, name)
Select id, name from Table2A Where Id not in (select ID from table2)


--
Brian Bushay (TeamB)
Bbushay@xxxxxxxxx


.

Relevant Pages

  • Re: ASP - FROM statement slows down connection to database
    ... Open your database in Access, switch to the Queries tab, create a new query ... in Design View without choosing a table, swtich to SQL View, paste the sql ... connection string rather than using an ... recordset open statements. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Delphi ADO databse newbie question
    ... I use ADO and Delphi has some great TADO wrappers, Check out the ADO page on ... I would use a datamodule and call it something like ... Drop a TADOQuery on your datamodule and set the connection property to ... When the active property of the query object is set to true all your records ...
    (alt.comp.lang.borland-delphi)
  • Re: Pessimistic locking with approles.
    ... The "fix" is to disable connection pooling in ADO in the connection ... security or SQLS security, and it has nothing whatsoever to do with ... SQL Application Role Errors with OLE DB Resource Pooling ...
    (microsoft.public.sqlserver.security)
  • Re: Perl ODBC complex query example needed
    ... newsgroup about SQL would be better for that. ... I am guessing that you probably want to access an ODBC database ... the connection string will be different). ... > sql connection is in scope for the query. ...
    (comp.lang.perl.misc)
  • Re: SQL incorporation with Excel
    ... Excel 2002 VBA by Bullen, Green, Bovey and Rosenberg has a fairly good ... Set a reference to ADO 2.7 in your vb project. ... connection string to find the correct way to connect. ... Our accounting system uses an SQL ...
    (microsoft.public.excel.programming)