Results 1 to 5 of 5

Thread: Compare Script

  1. #1
    Join Date
    Jul 2004
    Posts
    191

    Unanswered: Compare Script

    I need to write a compare script to compare the data that two databases may have in common and to delete the records in the first database. And the records are are not incommon to be moved over to the second database.

    Thanks

    Lystra

  2. #2
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    Davide Mauri
    http://www.davidemauri.it

  3. #3
    Join Date
    Jul 2004
    Posts
    191
    Thanks, but theres resistions on my PC

    I was able to write this:

    Select ClientHost,Username,
    Logtime,Service, Machine, ServerIP
    ,ProcessingTime, BytesRecvd,BytesSent,
    ServiceStatus, Win32Status, Operation,
    Target, Parameters, Department
    From IISLOG.dbo.IISLOG as a where NOT EXISTS
    (select ClientHost,Username,
    Logtime,Service, Machine, ServerIP
    ,ProcessingTime, BytesRecvd,BytesSent,
    ServiceStatus, Win32Status, Operation,
    Target, Parameters, Department
    from IISLOG as b
    Where a.ClientHost =b.ClientHost
    and a.Username = b.Username
    and a.Logtime = b.Logtime
    and a.Service = b.Service
    and a.Machine = b.Machine
    and a.ServerIP = b.ServerIP
    and a.ProcessingTime = b.ProcessingTime
    and a.BytesRecvd = b.BytesRecvd
    and a.BytesSent = b.BytesSent
    and a.serviceStatus = b.ServiceStatus
    and a.Win32Status = b.Win32Status
    and a.Operation = b.Operation
    and a.Target = b.Target
    and a.Parameters = b.Parameters
    and a.Department = b.Department)

  4. #4
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    Don't you have a primary key?
    Davide Mauri
    http://www.davidemauri.it

  5. #5
    Join Date
    Jul 2004
    Posts
    191
    That's the problem because once the data is archive the Id which is the primary key is not archive. But it does have a ID field in the second database. It kind of like ID autonumber in MS access.

    This is the code it uses:

    CREATE PROCEDURE sp_archiveLOG AS
    declare @Today varchar(10)
    select @Today = convert(varchar(10),getdate(),101)
    --select @Today = '2002-03-30'
    /*
    copy updated rows to archive database
    */
    insert IISLOG_ARCHIVE2004..iislog
    select clienthost,username,logtime,service,machine,server ip,processingtime,bytesrecvd,bytessent,servicestat us,win32status,operation,target,parameters,departm ent from IISLOG..iislog
    where department is not null
    and ( LogTime >= DateAdd(day, -1, @Today) AND LogTime < @Today )
    GO

    As you see it doesn't archive the id number

    The next code deletes the whole record

    CREATE PROCEDURE sp_cleanupLOG AS
    declare @Today varchar(10)
    select @Today = convert(varchar(10),getdate(),101)
    --select @Today = '2002-03-30'
    /*
    delete rows from current iislog table
    */
    delete from IISLOG..iislog
    where ( LogTime >= DateAdd(day, -1, @Today) AND LogTime < @Today )

    So for me to compare I would have to compare the whole field.

    Lystra

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •