SQL Query to delete all the transactions older than year 2016

(0) Share

Report

Posted on by Community Member

Dear All,

Need sql query help to delete all the transactions older than 2016. We just want to keep year 2016 data in the database.

Thanks & Regards,

Upendra Nath M.

*This post is locked for comments

I have the same question (0)

All responses (16)

Answers (1)

NickZnavor 715 on at

Like (0)

Report

Please make sure you backup first!

Here is a script that you can run based on the batch numbers. Be aware this deletes the data used by reports also, so no sales data will show up prior to 2016, or whatever batch number you set SELECT @BatchNumberKeep = 12028 to. Also make sure to change the last part, YOURDATABASENAME, to the correct name

DECLARE @BatchNumberKeep int

SELECT @BatchNumberKeep = 12028

DELETE Shipping FROM [Transaction]
     WHERE BatchNumber < @BatchNumberKeep AND Shipping.TransactionNumber =

[Transaction].TransactionNumber

--DELETE TransactionEntry FROM [Transaction]
--     WHERE BatchNumber < @BatchNumberKeep AND

--TransactionEntry.TransactionNumber = [Transaction].TransactionNumber

DELETE TaxEntry FROM [Transaction]
     WHERE BatchNumber < @BatchNumberKeep AND TaxEntry.TransactionNumber =

[Transaction].TransactionNumber

DELETE SignatureCapture FROM TenderEntry
     WHERE BatchNumber < @BatchNumberKeep AND

SignatureCapture.TenderEntryID = TenderEntry.ID

DELETE [Transaction] WHERE BatchNumber < @BatchNumberKeep

DELETE Journal WHERE BatchNumber < @BatchNumberKeep

DELETE TaxTotals WHERE BatchNumber < @BatchNumberKeep

DELETE TenderEntry WHERE BatchNumber < @BatchNumberKeep

DELETE TenderTotals WHERE BatchNumber < @BatchNumberKeep

DELETE Batch WHERE BatchNumber < @BatchNumberKeep
--DBCC SHRINKDATABASE (YOURDATABASENAME)

This process will take awhile depending on the data you have. You should do it when the store is not open. Also, BACKUP!!!. :P

And I always delete small range sets of batches, instead of all at once

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you Nick Znavor. It worked for me.

Thanks & Regards,

Upendra Nath M.

Was this reply helpful? Yes No
Verified answer

archelle16 1,743 on at

Like (0)

Report

however i do not advice deleting transaction history. You could create a data warehouse.. and aggregate all your data to a simple and manageable records.

btw, please mark this thread as verified if this becomes completed.

Was this reply helpful? Yes No
Suggested answer

Devanand Soodeen 426 on at

Like (0)

Report

sql query- delete from journal where time <'01.01.2015'

please back up before deleting

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Archelle, could you please explain more detail about data warehouse? how will I create data warehouse and how do I access to that data? Just using SQL?

Was this reply helpful? Yes No
archelle16 1,743 on at

Like (0)

Report

hello dan, a datawarehouse is a system that is used to provide data analysis. I have mentioned this because it easily archive our data rather than deleting it. I have also noticed that RMS reports are querying directly to the transactional database, such reports include aggregate queries which causes traffic/blocking to other applications especially in HQ. A data warehouse is a separate database wherein reports are easily generated. Yes, a datawarehouse can run multiple sql jobs directly from the transactional database (live) to our DW.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Brilliant Nick...put SQL statements in the customer forum so everyone can delete their history instead of archiving it or moving backups to recover disk space. Good plan, kudos for mentioning don't forget to backup though. Yes, I know you need a third party archive tool for RMS and I also know a 4TB external hard drive is about $100.00. [tag:fail]

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

It seems unusual my friend that you don't condone deleting data, but ask in a public forum how to do it?

Was this reply helpful? Yes No
TheNeos 1,347 on at

Like (0)

Report

Whats wrong with you "Butch" or "freeman"?

Nick's first sentence is "Backup first", Upendah's post was not about recovering space. There are situations that only current year needs to be.

So thanks Nick for your efforts and have a merry Xmas.

Neos

Was this reply helpful? Yes No
NickZnavor 715 on at

Like (0)

Report

Right back at ya TheNeos Happy Holidays!!!

I didn't have a mature response to his comments so I didn't reply, lol

Was this reply helpful? Yes No