?
Solved

Sql script change

Posted on 2008-06-10
5
Medium Priority
?
205 Views
Last Modified: 2010-04-21
Hello,
Since we are to keep each question only related to itself I am opening a follow-up question to my earlier SQL Script question.

In short I have a script that runs perfectly right now moving data from one db to another.  The only glitch with this script is that it requires the user to change the "before date".  I want to apply the fix from my earlier "purge" question to this as well.

Does anyone see a problem going from this:
SET @CutoffDate='1/1/2006'
And changing it to this (wanting the script to move anything more than 12 months old):
set @cutoffdate WHERE [date] < dateadd(month, -12, getdate())

Unfortunately I have no way to test this without actually running the script so I thought it safer to look like an idiot and ask what I am assuming is a pretty obvious question.

So thoughts?
Brian
0
Comment
Question by:KollBrian
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1000 total points
ID: 21753745
looks good...but you'll need this:

delete from yourtablename
WHERE [date] < dateadd(month, -12, getdate())
0
 

Author Comment

by:KollBrian
ID: 21754103
In this tables case we are actually not deleting any data.  The script is setup to move the tables data from the "current completed" to the "prior completed" database.  So it grabs all the items older than the date we typed in and moves those rows to the archive database.  

This is why I am trying to use the new formating so we can set it as a scheduled task instead of having to do this manually.  Automation is the key :).

so the command change will work?

Brian
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21754178
Yep...looks good.  I thought you were deleting data.  Yours should be fine.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21754221
> The script is setup to move the tables data from the "current completed" to the "prior completed" database.  So it grabs all the items older than the date we typed in and moves those rows to the archive database.  


if you use SQL 2005, you can do that quite nicely with the OUTPUT statement:
http://weblogs.sqlteam.com/mladenp/archive/2006/06/08/10122.aspx


--- declare a "temp" table with the same structure than the actual source table
declare @deleted table (id int, name varchar(100)) 
declare @err int 
begin transaction
--- delete the data you want to move from the source table, getting the "copy" into the @delete table
DELETE yourtable
OUTPUT DELETE.* INTO @deleted
WHERE [date] < dateadd(month, -12, getdate()) 
SET @err = @@error 
IF @err = 0
BEGIN
  -- insert the previously delete rows into the archive table
  INSERT INTO archive_table ( id, name ) 
    SELECT id, name 
      FROM @deleted 
  SET @err = @@error
END 
IF @err = 0
BEGIN
  COMMIT TRANSACTION
END
ELSE
BEGIN
  ROLLBACK TRANSACTION
END

Open in new window

0
 

Author Closing Comment

by:KollBrian
ID: 31465867
just what I needed, independent verification of the script. Thanks :)
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question