Keep performance high

I always ssem to want things I cannot find in books about the subject.

Here's my challenge!

I've created a MsAccess Frontend/MsSQL backend for a callcenter. In the Main table there are more than 200.000 records so I want to nibble from the botton and put let's say 100.000 records in a new table (and delete in the main). But I want this new table with the backup records to be in a new database so I can create an new frontend to search in the history-records.
Every month I woul like to take of the main table some 20.000 records because that's the monthly average in growth and paste these records in the backup table in the ohter database. So performance is no issue and history is maintained.

Anybody with any ideas or pointer to solutions? All is appreciated highly.

If this is something you want run monthly you could set it up on your SQL Server as a Job. This will allow you to write a query that will transfer the data you want, plus remove the data after it has been transferred while allowing SQL Server to execute this job at the necessary time.

That's exactly want I want but unfortunately am not able to do myself so i'm looking for examples on how to do this

I've created the BCK-DB manually and I've restored a backup from the production db into the backup-db to test things.
I'm relatively new to this kind of SQLServer stuff.

To create a job:

In the Enterprise Manager...
Expand the Server/Management/SQL Server Agent
Right click on Jobs and choose New Job
Give it a name and then click the "Steps" tab
Click the New Step Button
Give it a name
Type should be Transact-SQL Script
Select the proper database in the Database drop down
In the Command text box put your SQL. It would go something like this:

INSERT INTO backuptable SELECT TOP 100000 * FROM livetable ORDER BY identityfield desc;

DELETE FROM livetable WHERE identityfield IN (SELECT TOP 100000 * FROM livetable ORDER BY identityfield desc);

Click Ok and then Choose the Schedule Tab.
Click the New Schedule button to set a time for this to run.

I hope that helps. Make sure to try this on test tables first. If for some reason the INSERT fails but the DELETE doesn't you'll be either grateful for having a good backup routine or kicking yourself for not.

Ok,
I've succeeded in copying rows from and into with;
insert into PSCBCK.dbo.tblLeeg2 select top 10 * from PSCBCK.dbo.TblAlles order by volgnummer

But if I try your second command it results in an error.

When I try to a
Delete from PSCBCK.dbo.TblAlles select top 10 it deletes all the records

So any more tips are welcome and if you can recommend a book on MsAccess/SQL I'm looking forward to reading them.

Divit

The problem with his second query is the *. If your going to use an "IN" like that, you need to do a query that returns 1 column. So it's

DELETE FROM livetable WHERE identityfield IN (SELECT TOP 100000 identityfield FROM livetable ORDER BY identityfield DESC;

However you may find that a "WHERE identityfield IN ([list])" query with a list of 100000 is just a little innefficient. It should be a lot faster to say

DELETE FROM livetable WHERE identityfield >= (SELECT TOP 1 identityfield FROM backuptable ORDER BY identityfield ASC);

That is, you select the value of the identityfield of the 100000th row (the last row, which should be the lowest- or highest, depending on how your identityfield is organized and assigned) that you just inserted into the backup field, and delete everything greater than that (all of which you just backed up).

Thanks for your help. I've succeeded in deleting the rows I've copied first to the other table. The code is on another machine but I've deleted all the records with the same identity-number, using it in the subquery.

Now on to get it all organised in a nice scheduled job.

Divit