February 4, 2015 at 8:38 am
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.
February 4, 2015 at 8:45 am
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/
February 4, 2015 at 8:47 am
What other detail do you want me to provide? I will provide.
February 4, 2015 at 8:54 am
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/
February 4, 2015 at 9:03 am
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
February 4, 2015 at 9:18 am
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/
February 4, 2015 at 9:21 am
I will try it and get back. Thanks!
February 4, 2015 at 10:24 am
It worked. I was able to modify the script you provided for archiving also.
Thanks for your help.
February 4, 2015 at 10:25 am
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/
February 12, 2015 at 8:19 pm
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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply