Post reply

Trying to update one table from another on-the-fly

  • April 5, 2007 at 2:19 pm

    #153511

    Hello folks:

     

    I need a little direction with the following situation.  I have a Workstation table that contains, among other columns, the service tag (unique to select on), computer name and computer model for our installed base.  This table was created and maintained manually as new computers are purchased and old systems taken out of service. 

     

    I have a script that runs on the network that collects user IP addresses, user names, along with the service tags, computer names, and computer models as the users logon to the network.  I collect this CSV report and load it into a table in the database.  Since this is updated every time someone logs in, it is more current than what is in the Workstation table.

     

    What I need to do is create a way to update the workstation table with the data collected.  I want to key on the service tag number and update the computer name and computer model if they are different.  I can create an update script and run it either manually or create a job, but I’m looking for a better way to accomplish this.

     

    We have an Access .ADP program for the inventory.  I am currently using a form to display the Workstation information, and I have modified the form to display the collected data. 

     

    Is there a way to create an update trigger to update the Workstation data with the collected data as each record is brought up on the form?  I know it’s not the most effective way to go about this, but it would guarantee that the information is current.

     

    If anyone has a better way to do this please let me know.

  • April 5, 2007 at 4:00 pm

    #698935

    I'm using views in similar situations.

    View returns values from the latest record for the category.

    Work perfectly.

    Even with 100k records in log table report is generated in less than 1 second.

    _____________
    Code for TallyGenerator

  • April 5, 2007 at 4:17 pm

    #698937

    You can create an INSERT trigger on the table into which you insert the CSV data from your network logon.  Something along the lines of 

    CREATE TRIGGER AfterInsertLogonCSV ON LogonCSVAFTER INSERTAS
    -- for the ServiceTag just inserted into your logon table,
    -- if the model & name are not the same as now in dbo.Workstation,
    -- do the update 
    IF NOT EXISTS (SELECT a.* FROM dbo.Workstations AS a
    WHERE ServiceTag = b.ServiceTag AND ComputerModel = b.ComputerModel AND ComputerName = b.ComputerName
    JOIN Inserted AS b ON a.ServiceTag = b.ServiceTag)
    UPDATE a 
    SET ComputerModel = b.ComputerModel,
    ComputerName = b.ComputerName
    FROM dbo.Workstations AS a
    JOIN dbo.LogonCSV AS b ON a.ServiceTag = b.ServiceTag
    JOIN Inserted AS c ON c.ServiceTag = b.ServiceTag
    -- or words to that effect
    There is no "i" in team, but idiot has two.

  • April 5, 2007 at 4:21 pm

    #698938

    OK, that was weird.  Let's try it again.

    You can create an INSERT trigger on the table into which you insert the CSV data from your network logon.  Something along the lines of 

    CREATE TRIGGER AfterInsertLogonCSV ON LogonCSV
    AFTER INSERT AS
    -- for the ServiceTag just inserted into your logon table,
    -- if the model & name are not the same as now in dbo.Workstation,
    -- do the update 
    IF NOT EXISTS (SELECT a.* FROM dbo.Workstations AS a
    WHERE ComputerModel = b.ComputerModel AND ComputerName = b.ComputerName
    JOIN Inserted AS b ON a.ServiceTag = b.ServiceTag)
    UPDATE a 
    SET ComputerModel = b.ComputerModel,
    ComputerName = b.ComputerName
    FROM dbo.Workstations AS a
    JOIN dbo.LogonCSV AS b ON a.ServiceTag = b.ServiceTag
    JOIN Inserted AS c ON c.ServiceTag = b.ServiceTag
    There is no "i" in team, but idiot has two.

  • April 6, 2007 at 4:51 pm

    #699127

    Thanks Dave. 

    I'll give it a try.  Tha sounds exactly what I am looking for, a trigger.  Didn't get the syntax right.  I'll let you know the outcome.  It's work related so if I get a chance I'll remote in and try it.

     

Viewing 5 posts - 1 through 4 (of 4 total)

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