Solved

Sql script change

Posted on 2008-06-10
5
198 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 142

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Express connecting form remote error 26 7 39
Pivot Query Problem 9 37
Isolation level in SQL server 3 47
SQL Syntax:  How to Find Commonality Among Similar Results 2 43
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now