• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Sql script change

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
KollBrian
Asked:
KollBrian
  • 2
  • 2
1 Solution
 
chapmandewCommented:
looks good...but you'll need this:

delete from yourtablename
WHERE [date] < dateadd(month, -12, getdate())
0
 
KollBrianAuthor Commented:
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
 
chapmandewCommented:
Yep...looks good.  I thought you were deleting data.  Yours should be fine.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> 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
 
KollBrianAuthor Commented:
just what I needed, independent verification of the script. Thanks :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now