Importing / Updating from another DB to MS SQL

Discussion in 'microsoft.public.sqlserver.replication' started by Jordan, May 25, 2010.

  1. Jordan

    Jordan Guest

    We have a MS SQL 2000 server and an Pervasive 2000i SP4 Server on seperate
    boxes. There is a table on the Pervasive box that has a list of parts with
    descriptions and other information that I need to have partially syncronized
    wtih my MS box.

    Right now I have an MS Access front end connecting to both tables via ODBC
    that has an append query and an update query to add and update just the
    information I need to the MS SQL server:

    1. Get all the new Part Numbers, Descriptions, and Price out of [Part
    Master] on the Pervasive box and append them to the table on the MSSQL box
    if the part does not already:

    INSERT INTO tblParts ( PartID, Description1, Description2, Cost )
    SELECT [Part Master].PRTNUM_01,
    [Part Master].PMDES1_01,
    [Part Master].PMDES2_01,
    [Part Master].COST_01
    FROM [Part Master] LEFT JOIN Parts ON [Part Master].PRTNUM_01 = Parts.PartID
    WHERE (((Parts.PartID) Is Null));

    2. Update all the Part Descriptions on the MSSQL box with all the current
    descriptions from the Pervasive box.

    UPDATE [Part Master] INNER JOIN Parts
    ON [Part Master].PRTNUM_01 = Parts.PartID
    SET Parts.Discription1 = [PMDES1_01],
    Parts.Description2 = [PMDES2_01],
    Parts.Cost = [COST_01],
    Parts.PlannerID = [PLANID_01];

    The MS SQL server has the Pervasive client installed and ODBC Connection
    setup. Is it possible for me to have the MSSQL server run the two updates
    itself at night rather than have to use the Access queries?
     
    Jordan, May 25, 2010
    #1
    1. Advertisements

  2. Jordan

    Paul Ibison Guest

    Best to repost this in the programming group as it is not related the SQL
    Server replication.
    Thanks,
    Paul Ibison
     
    Paul Ibison, Jun 15, 2010
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.