HOW TO ARCHIVE AND DELETE OLD RECORD IN A TABLE THAT HAS NO DATE COLUMN

Question

HOW TO ARCHIVE AND DELETE OLD RECORD IN A TABLE THAT HAS NO DATE COLUMN

deebabat

Ten Centuries

Points: 1125
More actions
February 4, 2015 at 8:38 am

#300850

I have these two tables Log and CategoryLog, I need to archive records older than 13 months in these two tables to two separate tables and then delete the archived records from Log and CategoryLog tables. The problem is that only 'Log' table has a date column, the other table CategoryLog does not have any date column. But the two tables are connected by a column(LogID). Please I need help on how to archive the data and then delete the archive data from both tables.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286602 More actions · Answer 1

deebabat (2/4/2015)
I have these two tables Log and CategoryLog, I need to archive records older than 13 months in these two tables to two separate tables and then delete the archived records from Log and CategoryLog tables. The problem is that only 'Log' table has a date column, the other table CategoryLog does not have any date column. But the two tables are connected by a column(LogID). Please I need help on how to archive the data and then delete the archive data from both tables.

Since you didn't provide any details we can't provide much help here. You would have to use the Log table and join to the CategoryLog table to find the rows to archive/delete.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

deebabat Ten Centuries Points: 1125 More actions · Answer 2

What other detail do you want me to provide? I will provide.

Sean Lange SSC Guru Points: 286602 More actions · Answer 3

deebabat (2/4/2015)
What other detail do you want me to provide? I will provide.

Since presumably you want help with the code we need to know what the table structures are. The best way is to post create table scripts so we have something to work with. Please take a few minutes and read the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

deebabat Ten Centuries Points: 1125 More actions · Answer 4

Here is the first table with a date column:

CREATE TABLE [dbo].[Log](

[LogID] [int] IDENTITY(1,1) NOT NULL,

[EventID] [int] NULL,

[HandlingInstanceId] [uniqueidentifier] NULL,

[UserId] [uniqueidentifier] NULL,

[Priority] [int] NOT NULL,

[Severity] [nvarchar](32) NOT NULL,

[Title] [nvarchar](256) NOT NULL,

[Timestamp] [datetime] NOT NULL,

[MachineName] [nvarchar](32) NOT NULL,

[AppDomainName] [nvarchar](512) NOT NULL,

[ProcessID] [nvarchar](256) NOT NULL,

[ProcessName] [nvarchar](512) NOT NULL,

[ThreadName] [nvarchar](512) NULL,

[Win32ThreadId] [nvarchar](128) NULL,

[Message] [nvarchar](1500) NULL,

[FormattedMessage] [ntext] NULL,

CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED

(

[LogID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

The second table with no date column:

CREATE TABLE [dbo].[CategoryLog](

[CategoryLogID] [int] IDENTITY(1,1) NOT NULL,

[CategoryID] [int] NOT NULL,

[LogID] [int] NOT NULL,

CONSTRAINT [PK_CategoryLog] PRIMARY KEY CLUSTERED

(

[CategoryLogID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CategoryLog] WITH CHECK ADD CONSTRAINT [FK_CategoryLog_Category] FOREIGN KEY([CategoryID])

REFERENCES [dbo].[Category] ([CategoryID])

GO

ALTER TABLE [dbo].[CategoryLog] CHECK CONSTRAINT [FK_CategoryLog_Category]

GO

ALTER TABLE [dbo].[CategoryLog] WITH CHECK ADD CONSTRAINT [FK_CategoryLog_Log] FOREIGN KEY([LogID])

REFERENCES [dbo].[Log] ([LogID])

GO

ALTER TABLE [dbo].[CategoryLog] CHECK CONSTRAINT [FK_CategoryLog_Log]

GO

Sean Lange SSC Guru Points: 286602 More actions · Answer 5

Not quite sure how you want to archive these but this should help with the delete. You can use a similar query for the archiving, but of course you should archive before you delete.

The date predicate may not be what you want either because I don't know how you are defining older than 13 months.

delete CategoryLog

where CategoryLogID in

(

select cl.CategoryLogID

from Log l

join CategoryLog cl on cl.LogID = l.LogID

where l.Timestamp < DATEADD(MONTH, -13, getdate())

)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

deebabat Ten Centuries Points: 1125 More actions · Answer 6

deebabat

Ten Centuries

Points: 1125

February 4, 2015 at 9:21 am

#1775376

I will try it and get back. Thanks!

deebabat Ten Centuries Points: 1125 More actions · Answer 7

It worked. I was able to modify the script you provided for archiving also.

Thanks for your help.

Sean Lange SSC Guru Points: 286602 More actions · Answer 8

deebabat (2/4/2015)
It worked. I was able to modify the script you provided for archiving also.
Thanks for your help.

You are welcome. Glad that worked for you and thanks for letting me know.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 9

deebabat (2/4/2015)
I have these two tables Log and CategoryLog, I need to archive records older than 13 months in these two tables to two separate tables and then delete the archived records from Log and CategoryLog tables. The problem is that only 'Log' table has a date column, the other table CategoryLog does not have any date column. But the two tables are connected by a column(LogID). Please I need help on how to archive the data and then delete the archive data from both tables.

Out of curiosity and with a possible suggestion in mind, what version of SQL Server to you have? Standard or Enterprise?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

HOW TO ARCHIVE AND DELETE OLD RECORD IN A TABLE THAT HAS NO DATE COLUMN

Cookies on SQLServerCentral