Board index » delphi » Insert Data to MS-SQL TABLE

Insert Data to MS-SQL TABLE

Hi Experts,

I have a problem to insert data in sqlTABLE.    I use tmACC query component
(as shown in my code) to get the SN and BID from accTABLE (MS-Access Table)
which will return about 500,000 rows.    I want to insert these 500k rows to
sqlTABLE (MS-SQL) so I code it as shown below.   It spend a very long.......
time and I could not be pateint enough waiting for it done.     I don't know
if I do any mistake or it is normal if I code like this.     So I would like
to know if there is any other methods to make in done in a short of time.
(SN is Char(8) and BID is as Integer Data type).     I hope to hear from
anyone of you soon.   Thank in advance.

Regards,

AMIT

Sample CODE:

  tmdsACC:=TDataSource.Create(self);
  tmACC:=TADOQuery.Create(self);
  tmdsACC.DataSet:=tmACC;
  tmACC.Connection:=accConnection;
  with tmACC.SQL do
  begin
    Clear;
    add('SELECT SN, BID');
    add('FROM accTABLE');
  end;
  tmACC.Open;

  tmSQL:=TADOQuery.Create(self);
  tmSQL.Connection:=sqlConnection;
  with tmSQL.SQL do
  begin
    Clear;
    add('INSERT INTO sqlTABLE (');
    add('  SN, BID)');
    add('VALUES (:SN, :BID)');
  end;
  tmSQL.DataSource:=tmdsACC;
  while not tmACC.eof do
  begin
    tmSQL.ExecSQL;
    tmACC.Next;
  end;

 

Re:Insert Data to MS-SQL TABLE


Hmm.. does this really work?
Can you do something similar to master detail approach, except that the
"detail" is an INSERT statement?

Anyway, my answer to your question is, why don't you do a direct table
insert instead?

INSERT INTO sqlTABLE (SN, BID) SELECT SN, BID FROM accTABLE

This is way faster than the approach you did.

Mio Nino Marquez

Re:Insert Data to MS-SQL TABLE


You can use linked servers or ad-hoc connection form SQLServer (OPENROWSET
function) and do this in single insert statement.

Stanko

Re:Insert Data to MS-SQL TABLE


Thank you for your reply.    I try to find out some more info about linked
server which should be used for referencing heterogeneous data source.    I
think this might be my solution.

AMIT.

Quote
"Stanko Plohl" <sta...@vizija.si> wrote in message news:3b42e245_2@dnews...
> You can use linked servers or ad-hoc connection form SQLServer (OPENROWSET
> function) and do this in single insert statement.

> Stanko

Re:Insert Data to MS-SQL TABLE


Thank you for your reply.    But I think it won't work because accTABLE
(MS-Access)  and sqlTABLE (MS-SQL) are on different connection.

AMIT.

"Mio Nino Marquez" <mionino[nospam]@apsilontech.com> wrote in message
news:3b42e0c5$1_1@dnews...

Quote
> Hmm.. does this really work?
> Can you do something similar to master detail approach, except that the
> "detail" is an INSERT statement?

> Anyway, my answer to your question is, why don't you do a direct table
> insert instead?

> INSERT INTO sqlTABLE (SN, BID) SELECT SN, BID FROM accTABLE

> This is way faster than the approach you did.

> Mio Nino Marquez

Re:Insert Data to MS-SQL TABLE


Books Online, index OPENROWSET for ad-hoc connection. Linked server is easy
to maintain with Enterprise Manager and not so easy with stored procedures
(sp_addlinkedserver, sp_addlinkedsrvlogin...).

Stanko

Quote
"AMIT" <a...@ksc.th.com> wrote in message news:3b434b1c_1@dnews...
> Thank you for your reply.    I try to find out some more info about linked
> server which should be used for referencing heterogeneous data source.
I
> think this might be my solution.

> AMIT.

Re:Insert Data to MS-SQL TABLE


I try to code as following and I got the error when I use this code with a
big table (500k rows) and it will be OK if the table is small.  The error is
Project Project1.exe raised exception class EOleException with message
'Timeout expired'. Process stopped. Use Step or Run to continue.

My Code:

  tmdsSQL:=TDataSource.Create(self);
  tmSQL:=TADOQuery.Create(self);
  tmSQL.Connection:=sqlDB;
  with tmSQL.SQL do
  begin
    Clear;
    add('INSERT INTO sqlTABLE (');
    add('  SN, BID)');
    add('SELECT SN, BID');
    add('FROM
OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''c:\Accesstable.mdb'';''Admin'';''''
,accTABLE)');
  end;
  tmSQL.Prepared;
  tmSQL.ExecSQL;
  tmSQL.Free;

I try to find what the problem is.    I found that I created this database
using the default value of CREATE DATABASE command and the following is the
code I used to  create this database

  with qrySQL.SQL do
  begin
    Clear;
    add('CREATE DATABASE sqlDATA');
  end;
  qrySQL.Prepared;
  qrySQL.ExecSQL;

I try to change the way I create the database using the following code.

  with qrySQL.SQL do
  begin
    Clear;
    add('CREATE DATABASE '+dbname);
    add('ON PRIMARY');
    add('(  NAME =sqlDATA_Primary,');
    add('   FILENAME = ''C:\Program Files\Microsoft SQL
Server\MSSQL\Data\sqlDATA.mdf'',');
    add('   SIZE = 400MB)');
  end;
  qrySQL.Prepared;
  qrySQL.ExecSQL;

By changing the size value, The error would not be occured when the size is
more than or equal to 400 MB and must not be greater than 800 MB because the
error would be occured again.    I don't understand what is the cause of
this problem.     And if the initial size of the database is so important
how can I know what size should it be when I create a database.

I hope to get reply  from anyone of you soon.   Thank in advance.

AMIT

 "Stanko Plohl" <sta...@vizija.si> wrote in message
news:3b43f549$1_2@dnews...

Quote
> Books Online, index OPENROWSET for ad-hoc connection. Linked server is
easy
> to maintain with Enterprise Manager and not so easy with stored procedures
> (sp_addlinkedserver, sp_addlinkedsrvlogin...).

> Stanko

> "AMIT" <a...@ksc.th.com> wrote in message news:3b434b1c_1@dnews...
> > Thank you for your reply.    I try to find out some more info about
linked
> > server which should be used for referencing heterogeneous data source.
> I
> > think this might be my solution.

> > AMIT.

Re:Insert Data to MS-SQL TABLE


Hi,

I don't think your problem is really the initial size of the DB. The problem
is the timeout value on the sql statement. You should increase it. The
reason that it appears that the initial size is the difference is that when
you create a database, the system allocates a file that is the same size as
your allocation. When you exceed that size, the db grows dynaically. I'd
guess this dynamic growth is your problem. It's just enough of a difference
in time to cause a time-out error.

Mike Walsh

Quote
"AMIT" <a...@ksc.th.com> wrote in message news:3b45eddf_2@dnews...
> I try to code as following and I got the error when I use this code with a
> big table (500k rows) and it will be OK if the table is small.  The error
is
> Project Project1.exe raised exception class EOleException with message
> 'Timeout expired'. Process stopped. Use Step or Run to continue.

> My Code:

>   tmdsSQL:=TDataSource.Create(self);
>   tmSQL:=TADOQuery.Create(self);
>   tmSQL.Connection:=sqlDB;
>   with tmSQL.SQL do
>   begin
>     Clear;
>     add('INSERT INTO sqlTABLE (');
>     add('  SN, BID)');
>     add('SELECT SN, BID');
>     add('FROM

OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''c:\Accesstable.mdb'';''Admin'';''''

- Show quoted text -

Quote
> ,accTABLE)');
>   end;
>   tmSQL.Prepared;
>   tmSQL.ExecSQL;
>   tmSQL.Free;

Re:Insert Data to MS-SQL TABLE


TAdoQuery has a timeout of 30 seconds.  You should use a TAdoCommand instead
and set the timeout property to whatever you want.

--
Michael Rodriguez
Compeat Restaurant Accounting Systems
http://www.compeat.com
m...@compeat.com

Quote
"AMIT" <a...@ksc.th.com> wrote in message news:3b45eddf_2@dnews...
> I try to code as following and I got the error when I use this code with a
> big table (500k rows) and it will be OK if the table is small.  The error
is
> Project Project1.exe raised exception class EOleException with message
> 'Timeout expired'. Process stopped. Use Step or Run to continue.

> My Code:

>   tmdsSQL:=TDataSource.Create(self);
>   tmSQL:=TADOQuery.Create(self);
>   tmSQL.Connection:=sqlDB;
>   with tmSQL.SQL do
>   begin
>     Clear;
>     add('INSERT INTO sqlTABLE (');
>     add('  SN, BID)');
>     add('SELECT SN, BID');
>     add('FROM

OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''c:\Accesstable.mdb'';''Admin'';''''

- Show quoted text -

Quote
> ,accTABLE)');
>   end;
>   tmSQL.Prepared;
>   tmSQL.ExecSQL;
>   tmSQL.Free;

Re:Insert Data to MS-SQL TABLE


Hi,
I had ever set the timeout value property of ADOConnection to a very big
number, the problem still occurred.   When I change the parameter of CREATE
DATABASE command and set size = 400 MB error is gone.

AMIT

Quote
"Mike Walsh" <te...@msllib.com> wrote in message news:3b45fcd3_2@dnews...

> Hi,

> I don't think your problem is really the initial size of the DB. The
problem
> is the timeout value on the sql statement. You should increase it. The
> reason that it appears that the initial size is the difference is that
when
> you create a database, the system allocates a file that is the same size
as
> your allocation. When you exceed that size, the db grows dynaically. I'd
> guess this dynamic growth is your problem. It's just enough of a
difference
> in time to cause a time-out error.

> Mike Walsh

Re:Insert Data to MS-SQL TABLE


Hi,
TADOQuery has no Command timeout property to set however I can set this
timeout value in the ADOConnection's Command timeout property.     Is this
not the same as using TADOCommand?

AMIT.

Quote
"Michael Rodriguez" <mike@yada_yada_yada.compeat.com> wrote in message

news:3b46023c$2_2@dnews...
Quote
> TAdoQuery has a timeout of 30 seconds.  You should use a TAdoCommand
instead
> and set the timeout property to whatever you want.

> --
> Michael Rodriguez
> Compeat Restaurant Accounting Systems
> http://www.compeat.com
> m...@compeat.com

> "AMIT" <a...@ksc.th.com> wrote in message news:3b45eddf_2@dnews...
> > I try to code as following and I got the error when I use this code with
a
> > big table (500k rows) and it will be OK if the table is small.  The
error
> is
> > Project Project1.exe raised exception class EOleException with message
> > 'Timeout expired'. Process stopped. Use Step or Run to continue.

> > My Code:

> >   tmdsSQL:=TDataSource.Create(self);
> >   tmSQL:=TADOQuery.Create(self);
> >   tmSQL.Connection:=sqlDB;
> >   with tmSQL.SQL do
> >   begin
> >     Clear;
> >     add('INSERT INTO sqlTABLE (');
> >     add('  SN, BID)');
> >     add('SELECT SN, BID');
> >     add('FROM

OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''c:\Accesstable.mdb'';''Admin'';''''

- Show quoted text -

Quote
> > ,accTABLE)');
> >   end;
> >   tmSQL.Prepared;
> >   tmSQL.ExecSQL;
> >   tmSQL.Free;

Re:Insert Data to MS-SQL TABLE


Quote
"AMIT" <a...@ksc.th.com> wrote in message news:3b46166e_2@dnews...
> Hi,
> TADOQuery has no Command timeout property to set however I can set this
> timeout value in the ADOConnection's Command timeout property.     Is this
> not the same as using TADOCommand?

Unfortunately, no.  Setting the timeout property on the connection only
affects sql statements run directly off of the connection object, i.e.
MyConnection.Execute('SELECT * FROM mytable').

You need to either switch to a TAdoCommand and set the timeout property
there, or do something like this to expose the timeout property of the
TAdoQuery manually and set it in code:

TAdoDataSet(MyQuery).CommandTimeout := 3000;

HTH,

--
Michael Rodriguez
Compeat Restaurant Accounting Systems
http://www.compeat.com
m...@compeat.com

Re:Insert Data to MS-SQL TABLE


Thank for your suggestion.

AMIT.

Quote
"Michael Rodriguez" <mike@yada_yada_yada.compeat.com> wrote in message

news:3b4625cb$1_2@dnews...
Quote
> "AMIT" <a...@ksc.th.com> wrote in message news:3b46166e_2@dnews...
> > Hi,
> > TADOQuery has no Command timeout property to set however I can set this
> > timeout value in the ADOConnection's Command timeout property.     Is
this
> > not the same as using TADOCommand?

> Unfortunately, no.  Setting the timeout property on the connection only
> affects sql statements run directly off of the connection object, i.e.
> MyConnection.Execute('SELECT * FROM mytable').

> You need to either switch to a TAdoCommand and set the timeout property
> there, or do something like this to expose the timeout property of the
> TAdoQuery manually and set it in code:

> TAdoDataSet(MyQuery).CommandTimeout := 3000;

> HTH,

> --
> Michael Rodriguez
> Compeat Restaurant Accounting Systems
> http://www.compeat.com
> m...@compeat.com

Other Threads