I have inherited a MS-SQL 6.5 system and brand to to it. One table has over 7million records, of which I need to purge out. It seems I can only sql-delete about 100,000 records at one time - safely, otherwise I get record locking errors. I would like to batch this process using the isql command-line tool.
How could I enter the isql command prompt using batch files? When I do "isql -U username", it prompts for the password. Is there anyway to specify the password within the batch? My plan is to do some kind of loop in a batch file to delete 100,000 records at a time. It takes about an hour for each.
One other related question. I am deleteing because of device space limits. When I delete a couple of millions records, will I see the database usage go down immediately, or do I have to compact, pack or something like that?