Archiving data

  • Thread starter Thread starter Steve Kelley
  • Start date Start date
S

Steve Kelley

What is considered the correct way to archive a table from one database
to another i.e. myDataBase.mdb -> myArchiveDataBase.mdb. In the past I
have used "insert into myArchiveDataBase.Results select * from
myDataBase.Results where ..." This always worked in VB6 using Jet 4 but
database access was different. I would like to do this in the '.Net'
paradigm but don't see how to do it without lots and lots of typing. The
table I want to archive has 33 fields (not my design and I'm not allowed
to change it) so having to create an InsertCommand object with all of
those parameters seems a bit much. Any advice will be greatly appreciated.
 
This should be done on the server, not on the client. Write a stored
procedure to do the work and send the data to another database on the same
server or on a linked server. I would probably accept a SELECT * to pick up
all columns... as in

SELECT * INTO OtherDB.dbo.MyArchiveTable WHERE <some condition>


--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
William said:
This should be done on the server, not on the client. Write a stored
procedure to do the work and send the data to another database on the
same server or on a linked server. I would probably accept a SELECT * to
pick up all columns... as in

SELECT * INTO OtherDB.dbo.MyArchiveTable WHERE <some condition>
Click to expand...
Thanks for the response. My situation is a bit different from most. We
produce an instrument and as the user uses the instrument the results
are stored in an Access database. There is no server per se, the
instrument control application is the only access the user has to the
data unless he/she has MS Access installed (most likely not). As the
database grows the users may want to archive some of the data and remove
the archived data from the parent database. I present the user with a
dialog box that allows him/her to select batches and a date range to
archive, build the constraints string then archive the data. Database
programing is not my forte and I have never created a stored procedure.
Given the additional info do you still recommend that approach?
 
Steve said:
Thanks for the response. My situation is a bit different from most. We
produce an instrument and as the user uses the instrument the results
are stored in an Access database. There is no server per se, the
instrument control application is the only access the user has to the
data unless he/she has MS Access installed (most likely not). As the
database grows the users may want to archive some of the data and remove
the archived data from the parent database. I present the user with a
dialog box that allows him/her to select batches and a date range to
archive, build the constraints string then archive the data. Database
programing is not my forte and I have never created a stored procedure.
Given the additional info do you still recommend that approach?
Click to expand...
More on the problem.

I have tried many different combinations of things and have so far been
able to get the results I want. When I have a query that works the
archive ends up in the same directory as the application. This is not
what I want. In pseudo sql what I need is:

insert into 'path to archive db.table' select * from table where...

The where clause is built based on the user responses as described in
the above paragraph from my previous post.

I get the same errors from trying to run a stored procedure as I do
trying it from application code.

Thanks in advance.
 
Oh, sorry, I thought you were using a ... (opinion deleted)
In this case, I still suggest using SSIS or at least SqlBulkCopy to upload
the archive data to a SQL Server and strip off the old rows with a
Delete...oh... right, the customer does not have a ...
This is like trying to play badminton in the hall closet.

I'm sure one of the JET heads will kick in and help.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
William said:
Oh, sorry, I thought you were using a ... (opinion deleted)
In this case, I still suggest using SSIS or at least SqlBulkCopy to
upload the archive data to a SQL Server and strip off the old rows with
a Delete...oh... right, the customer does not have a ...
This is like trying to play badminton in the hall closet.

I'm sure one of the JET heads will kick in and help.
Click to expand...

I never meant to multipost but I ended up with another thread on the
vb.net group with a different subject line ('insert into problem') but
the same issue. From that group came the answer. I needed to use the IN
operator to specify the database file as in:

CommandText = "insert into Results in 'C:\path name\Archive.mdb' select
* from Results where ..."

If the path as no spaces the 'IN' is not needed for some reason.
 
Back
Top