move query

[inungh]

Sometimes I need move records from one table to another table.

I always need to insert into and delete from 2 queries to complete the
task.

Are there any query to "MOVE" from source to destination tbales?


Your information is great appreciated,

 

[Bob Barrows [MVP]]

Sometimes I need move records from one table to another table.

I always need to insert into and delete from 2 queries to complete the
task.

Are there any query to "MOVE" from source to destination tbales?

No. The best you can do is automate the process either via a vba procedure
(recommended) or via a macro.

Is this operation for archiving purposes?

 

[inungh]

No. The best you can do is automate the process either via a vba procedure
(recommended) or via a macro.

Is this operation for archiving purposes?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Thanks for the information,
No it is not archiving purpose.
If it is archving, are there any easy way to do so?
Thanks again,

 

[Bob Barrows [MVP]]

Thanks for the information,
No it is not archiving purpose.

Then why does it need to be done? Let me rephrase that: are you sure it
needs to be done? Moving data from one table to another is not something
that is typically done in relational databases. In fact, many would go
on to say that the need/desire to do so implies a need to redesign the
application and/or database so it does not need to be performed.

If it is archving, are there any easy way to do so?

Umm, the answer already given does not change just because the purpose
of the operation changes.

 

[inungh]

Then why does it need to be done? Let me rephrase that: are you sure it
needs to be done? Moving data from one table to another is not something
that is typically done in relational databases. In fact, many would go
on to say that the need/desire to do so implies a need to redesign the
application and/or database so it does not need to be performed.


Umm, the answer already given does not change just because the purpose
of the operation changes.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.- Hide quoted text -

- Show quoted text -

Thanks again,
Actually, I just want to keep my working table as small as possible
and move working table to a history table when it is done,
Thechnically, it is a knid arching process.

Do you mean that there is no performance impact for a large table?

Thanks again,

 

[Bob Barrows [MVP]]

Thanks again,
Actually, I just want to keep my working table as small as possible
and move working table to a history table when it is done,
Thechnically, it is a knid arching process.

It certainly sounds like an archiving process to me.

Do you mean that there is no performance impact for a large table?

Define "large". ;-)

If you have your indexes set up correctly, and your queries constructed
to allow them to use those indexes, then performance should be good
until the table gets very large (and no, I won't define that ;-) )

Only testing can answer that question for you.

 

[John W. Vinson]

Do you mean that there is no performance impact for a large table?

Of course there is. Once a table gets above a million rows or so you'll start
to notice some slowing, even with good indexing and query design.

If you're worried about 1000 rows... don't. If you're worried about 10000
rows... try it first, optimize your indexes and queries, and only go to
archiving if you have a DEMONSTRATED need to do so.

 

[inungh]

Of course there is. Once a table gets above a million rows or so you'll start
to notice some slowing, even with good indexing and query design.

If you're worried about 1000 rows... don't. If you're worried about 10000
rows... try it first, optimize your indexes and queries, and only go to
archiving if you have a DEMONSTRATED need to do so.

Thanks for the information,
Speaking performance, how does Access to retrieve data.
For example, does Access send one millions records to fron end
application and let front end to choose record or only the one frony
requested?

Your help is great appreciated,

 

[Bob Barrows [MVP]]

Thanks for the information,
Speaking performance, how does Access to retrieve data.
For example, does Access send one millions records to fron end
application and let front end to choose record or only the one frony
requested?

It depends.
If there is a WHERE clause, only the record(s) that matches the criteria in
the WHERE clause will be sent to the client (front end).
Without a WHERE clause, the cursor type and cache size if the recordset you
open will control how many records are sent to the client at one time.
Ultimately, all of those millions of records might have to be processed in
the client to find the record you are actually looking for - use a WHERE
clause!!

 

[John W. Vinson]

Speaking performance, how does Access to retrieve data.
For example, does Access send one millions records to fron end
application and let front end to choose record or only the one frony
requested?

Depends on the table, the indexes, and the query. You should ideally have an
Index on each field or combination of fields which will be used in searching;
if there is such an index, Access will use it to retrieve only those rows
which match the criteria. Proper query design is important! It's quite
possible to write a query which defeats the use of indexes (e.g. searching a
date field by using Datevalue([datetimefield]) as a calculated field).