Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sql script change

Posted on 2008-06-10
5
Medium Priority
?
207 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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