Results 1 to 7 of 7

Thread: Help!!!!!

  1. #1
    Join Date
    Aug 2012
    Posts
    32

    Unanswered: Help!!!!!

    I have two tables in SQL

    TableA and TableB

    TableB is fed new data constantly and has
    Column1-Column8

    TableA has Column1, Column2, Column3 <--This is a static table whose information was originally pulled from TableB

    I am trying to find a query that I can put in a sub procedure to make TableA's column1, 2, and 3 data match TableB's. If I have a query that does this I can execute it within a stored procedure at run time in order that the data for tableA always matches tableB

    HELP!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    A few observations:
    "Help" is not a very useful subject for a forum post. Most people come here for help - that's a given. Something like "Synchronising tables" might have attracted more interest.
    If you need to update Table A from Table B on a regular basis, then it's not static. You would be better served by a view that pulls the relevant columns from Table B and using this where you currently use Table A. This would also reduce the size of the database, which may or may not be an issue for you.

    The easiest way to accomplish what you want (not the best - that would require knowing more about your data's structure) is to clear down Table A and regenerate it each time:
    Code:
    TRUNCATE TABLE TableA
    
    INSERT INTO TableA
        (Column1
    ,    Column2
    ,    Column3)
    SELECT
        Column1
    ,    Column2
    ,    Column3
    FROM
        TableB
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Aug 2012
    Posts
    32
    So if i use this query, and I have another column we will call it column4

    TableA has data for column1, column2, column3, and column4
    I want to update column1, 2, and 3's data according to tableB, without losing column4's data.

    will this query cause column4 to erase since column1 (the primary key) is being affected?

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Right, that's a totally different structure. You made no mention of keys in your original post.

    Yes, my original script will wipe out column4, since TRUNCATE clears everything in the table and you didn't mention column4! :P

    If there's a primary key involved (and please tell me that the key values are not subject to change), you don't want to delete and recreate. Instead, try:
    Code:
    UPDATE TableA
    SET
        a.Column2 = b.Column2
    ,   a.Column3 = b.Column3
    FROM
        TableA a
    INNER JOIN
        TableB b
    ON
        a.Column1 = b.Column1
    
    INSERT INTO TableA
       (Column1
    ,   Column2
    ,   Column3
    --, Column4    Not sure if you want this one or not
    )
    SELECT
        b.Column1
    ,   b.Column2
    ,   b.Column3
    --, b.Column4    Same principle applies as above
    FROM
        TableB b
    WHERE
        TableA.Column1 NOT IN
        (SELECT
            TableB.Column1
        )
    This is from memory, and might need tweaking.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Aug 2012
    Posts
    32
    I solved this....I was able to set parameters within my code that caused the stored procedures I used to be more simplistic. I.e. simple insert command, and simple update command.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Fair enough. Good luck with it!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Aug 2012
    Posts
    32
    thanks for all the help!

Posting Permissions

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