Post reply

Need help with store proc.....that great

  • June 20, 2011 at 11:41 am

    #239626

    Hi,

    I need to write a store proc to move the data from table1 to table2. Both tables are in different databases on same server . When data is moving from table1 to table2. I t should satisfy with the condition.

    What the condition is, the data which is in table1 should not cross 3 months. That mean the row or record which inserted today should move to table2 after 3 months. The columns in table1 are creation date, archive_date, archive_id and so on..... Take creation_date to check with condition. Creation_date mean row inserted date.

    I need just logic. If anyone help with this logic that would be great.

  • June 20, 2011 at 11:44 am

    #1341352

    Are you familiar with writing select statements in T-SQL?

    Build your select statement, including your Where clause, then put Insert into your target table on top of it.

    Are you moving rows or just copying them? That is, when you put a row in the second table, do you take it out of the first or leave it in both?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • June 20, 2011 at 11:47 am

    #1341356

    ok first the concept:

    you can use DATADD and DATEDIFF to fiddle with dates, keeping everything in the proper datetime variable.

    test this to understand the concept:

    select

    --midnight of TODAY.

    DATEADD(dd, DATEDIFF(dd,0,getdate()), 0),

    --older than 3 months before midnight of Today

    DATEADD(mm,-3,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0))

    now that we can find a date that is 3 months old, something like this should identify the records you might want to move:

    SELECT *

    FROM SOMETABLE

    --older than 3 months before midnight of Today

    WHERE CREATEDDATE <= DATEADD(mm,-3,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0))

    with that, you should be able to insert those records that are older than three months...

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply