Moving (or Archive ) records to another database (access / Sql Server). best practice ?

[nms]

hello,

i'd like to know the best practice for a utility i want to include in
my recent project. the requirements is like this,

There is a database (access or SQL Server 2005) we use as back-end. now
i do have some transaction table where i get 50-100+ records per
second.

1) this leads to tremendous growth of size of the MDB (access database
file) after some days.
and some time we need to do compact and repair immediately to resume
operations.

2) also we don't want to allow user to see transactions that are 1
month old. (for some reason) and for that reason i need to move all
records to some other table.
Here we may also opt for another field (yes/no) called "Archived" that
will help to decide archived status of records. BUT
In case ms-access is being used as back-end, i'd like move records in
some other database.
just to keep main db small in size.

vs2005 is being used for development in asp.net

now this is what i've been trying...

a) used "append query" to move records from one database "source.mdb"
to "archive.mdb" by,
i. INSERT INTO table_name IN '" & MapPath("db/archived.mdb") & "'
SELECT * from table_name
WHERE dtDateTime <= # var_date #"

ii. DELETE from table_name WHERE dtDateTime <= # var_date #"

--> i observed that after each operation both database grows in size.
and some times i need to compact and repair 'em.

b) i copy records one by one, using two adodb.recordsset.

--> this is not worth while in case large data. that i have. takes too
much time. ALSO db size increases a bit.


so what i want is the way to move records from one database to another.
connection strings are available in web.config for both.

i hope there will be way to cope up with this, regardless whatever is
being used as back-end.

p.s. here this post is worth to see BUT it's for mdb to sql server
only.

http://groups.google.co.in/group/mi...=gst&q=bulk+copy+data&rnum=1#5163a62c4d6eaf40







thanks in advance.

 

[William \(Bill\) Vaughn]

1) The key called "shift" on the keyboard makes it easier to read your
posts.
2) Check out SqlClient.SqlBulkCopy. It can import data from any DataReader
into SQL Server.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[nms]

Hello,

1) point noted

2) SqlClient.SqlBulkCopy is already mentioned there (see p.s.)

what's is needed is to move data from

1. access to access
2. access to sql
3. sql to access
4. sql to sql


thanks and regards,