Hi, I have a 7.0 Gb Database and I want to delete by year the transaccions and keep one data base for each year, rms2004, rms2005, etc. So every ear I delete tha transaccions. I'm using SQL Server offcourse.
Thanks
Hi, I have a 7.0 Gb Database and I want to delete by year the transaccions and keep one data base for each year, rms2004, rms2005, etc. So every ear I delete tha transaccions. I'm using SQL Server offcourse.
Thanks
You can do this using SQL Queries, but you might want to take a look at Retail Realm's Archiving utility which is all set up to do this for you. Contact your reseller for details or check out
Glenn Adams Tiber Creek C> Hi, I have a 7.0 Gb Database and I want to delete by year the transaccions
Did you ever get an answer for this?
Danny
"Gas28" wrote:
The answer was to contact Retail Realm coz they already have an archiving utility, building a script for archiving is big issue
Give this a look.. and test before you use it... no warranties from me. this did come from M$ a couple of years ago. /* DELETING SALES HISTORY FROM HQ */
--INTRUCTIONS
--===============================================================================--These queries will permanently delete all sales history
-- and related records PRIOR TO the specified Batch Number.
--
--Replace the value for @BatchNumberKeep in the SELECT statement
-- with the lowest Batch Number you wish to keep.
--
--EXAMPLE: SELECT @BatchNumberKeep = 50 -- All records before Batch Number 50 will be deleted
--
--==============================================================================--select max(batchnumber)
--from batch
--where closingtime < dateadd(mm,-7,getdate())
DECLARE @BatchNumberKeep int DECLARE @StoreID int declare @TransDate smalldatetime
SELECT @BatchNumberKeep = 0 SELECT @StoreID = 0
--Select @TransDate = '2003-10-10' -- use this format 'yyyy/mm/dd'
DELETE Shipping FROM [Transaction] WHERE BatchNumber < @BatchNumberKeep AND Shipping.StoreID = @StoreID AND Shipping.TransactionNumber = [Transaction].TransactionNumber --and convert(char(10),[Transaction].Time,111) = @TransDate
DELETE TransactionEntry FROM [Transaction] WHERE BatchNumber < @BatchNumberKeep AND TransactionEntry.StoreID = @StoreID AND TransactionEntry.TransactionNumber = [Transaction].TransactionNumber --and convert(char(10),[Transaction].Time,111) = @TransDate DELETE TaxEntry FROM [Transaction] WHERE BatchNumber < @BatchNumberKeep AND TaxEntry.StoreID = @StoreID AND TaxEntry.TransactionNumber = [Transaction].TransactionNumber --and convert(char(10),[Transaction].Time,111) = @TransDate DELETE SignatureCapture FROM TenderEntry WHERE BatchNumber < @BatchNumberKeep AND SignatureCapture.StoreID = @StoreID AND SignatureCapture.TenderEntryID = TenderEntry.ID
DELETE [Transaction] WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID --and convert(char(10),[Transaction].Time,111) = @TransDate DELETE Journal WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID --and convert(char(10), Time,111) = @TransDate
DELETE TaxTotals WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID --and convert(char(10),date,111) = @TransDate
DELETE TenderEntry WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID
DELETE TenderTotals WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID
DELETE Journal WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID
DELETE Batch WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID
Deleting transactions can be dangerous as you may levae behind orphan records. You may want to use the Retail Realm archiving module.
Afshin Alikhani - snipped-for-privacy@retailrealm.co.uk CEO - Retail Realm
--===============================================================================> --These queries will permanently delete all sales history
--===============================================================================>
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.