Post reply

Insert to another DB and unigue key issues, etc.

  • March 5, 2010 at 10:47 am

    #138474

    I have two Databases - One live the other Archive so they are same structure. This is a help desk calllog software. I need to move all the closed calls older the 1 year to the archive along with records in other tables that are journals, activity for the closed calls. Sturcutre

    Two DB's with same structure:

    Calllog

    PK - CALLID

    ClosedDATE

    CAllstatus

    etc.

    Journal table

    PK - CALLID

    HEATSEQ

    etc..

    I can get the live calllog records older the 1 year and closed over to the archive except wondering how do I deal with an issue with the PK and insert of duplicates? (the way the archive file was created in past there will be some duplicates CALLID's when I try to insert) Having issue getting the exists not exists to work along with a condition like older then 1 year and closed.

    Ok then once I have the CALLs I need in the archive I then want to use that archive table and compare it against the LIVE journal table and pull all the journals from live that have the same CALLID in the archive calllog.

    Issue with this is that the CALLID and HEATSEQ make up a unique clustered key so I need to check both of them for exists before I try to insert in.

    This is what I have -

    Use HEAT

    GO

    insert INTO heatarchive.dbo.calllog

    select * from heat.dbo.calllog

    WHERE heat.dbo.calllog.callstatus = 'closed' and heat.dbo.calllog.closeddate < GETDATE()- 365

    go

    INSERT INTO heatarchive.dbo.journal

    SELECT *

    FROM Heat.dbo.journal

    WHERE not exists (select * from heatarchive.dbo.journal

    where heatarchive.dbo.journal.callid = heat.dbo.journal.callid);

    First insert works as long as there isnt a duplicate CALLID will this continue if it finds one or error out.. If it does error how can I have it check for exists and the conditions of closed and 1 year?

    The second insert didnt work for my I know there is a journal for the CALLID in a testDB I created in it didnt insert it. Plus I need to check for the exist of the unique CALLID - HEATSEQ correct as there might be duplicates in both and they wont insert. Any help or points in right directions would be appreciated.

    Thanks,

    Jeff

  • March 5, 2010 at 12:22 pm

    #1129391

    Jeff i think you want to join the archiv table to the SELECt so you can exclude records already transferred;

    i don't know the PK of the tables, but this should help:

    insert INTO heatarchive.dbo.calllog

    select *

    from heat.dbo.calllog MyAlias

    LEFT OUTER JOIN heatarchive.dbo.calllog Archiv

    ON MyAlias.PK = Archiv.PK

    WHERE heat.dbo.calllog.callstatus = 'closed'

    and heat.dbo.calllog.closeddate < GETDATE()- 365

    and Archiv.PK IS NULL

    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 2 posts - 1 through 1 (of 1 total)

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