Solved

Sql script change

Posted on 2008-06-10
5
200 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
  • 2
  • 2
5 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 250 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql help 5 54
Two tables - Sum of values - What is the difference 31 54
Delete from table 6 47
How to place a condition in a filter criteria in t-sql (#2)? 10 56
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.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

840 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