Condional Update/insert from one table to other.

  • Hi,

    I have data in "temptable" and data in "originaltable".

    What I want to do, I will insert all the new rows from "temptable" to "originaltable" which "originaltable" don't have. I can manager this part.

    BUT

    addionally I want to update existing rows in "originaltable" table which "temptable" also have. update values with the values of "temptable". How can I achieve this part.

    at the end, I will delete all from "temptable". So next time I can fill it again, and start new insert/update process.

    Let me know, if you people need more details.

    Waiting for response.

    Regards,

    Azam.

  • Need a lot of additional information.

    1. What consitutes an updatable row ... comparing one columns entry, multiple columns in temptable with what columns in "originalTable"

    2. Supply if you can the definition of both tables.

    3. Supply the T-SQL statement you use to "add" a new row to the "originaltable"

    Without this information it is difficult to suggest a solution.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thanks for the response.

    1. I will compare 2 columns, ProductName and StoreName. Both table have equal number of fields with same names.

    2. Can't provide all fields, there are almost 35 fields in table. but you can assume it simple Products table, with 5/6 fields. ID,ProductName,StoreName,Price,ImageURL

    3. for insert, it is simple, I will insert all rows from "temptable" to "OrginialTable" which not exists in "originaltable". code is at

    http://www.sqlservercentral.com/Forums/Topic503363-8-1.aspx

    Insert is only 1 line query. but for update, I am not sure, I will be able to do in 1 line. I am afraid, I will need to use Cursors. But waiting for more good solutions.

    Hope these are enough details, still if you need, please let me know.

    Regards,

    Azam.

  • Not tested,

    but I think this kind of query can work....

    --------------------------------

    Update OriginalTable

    set

    OriginalTable.Field2=tempTable.field2,

    OriginalTable.Field3=tempTable.field3

    from tempTable

    where OriginalTable.Field1=tempTable.Field1

    ------------

    I was looking on OPENXML examples, when this idea comes in my mind. will test, will look forward for more replies till then.

    Regards,

    Azam.

  • I believe you have to use a two step procedure

    1. Update existing values:

    IF EXISTS(SELECT p.ProductName,p.StoreName FROM Products p, temptable t

    WHERE p.ProductName = t.productname AND p.storename = t.storename)

    BEGIN

    UPDATE Dbo.Products

    SET Price = t.price

    FROM Dbo.Products AS p

    JOIN Dbo.Temptable AS t

    ON p.ProductName = t.ProductName

    AND p.storename = t.storename

    END

    2. Add items where the combination of productname and storename are not in the products table

    INSERT INTO products (productname, storename, price)

    SELECT t.ProductName,t.storename,t.price

    FROM temptable t

    LEFT OUTER JOIN products p

    ON p.productname = t.productname AND p.storename = t.storename

    WHERE p.productname IS NULL OR p.storename IS NULL

    I hope this helps.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • If temp table contains ALL the final information that original table will have once you do all the inserts and updates to it, why not just "swap" tables?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • for

    No, temp table don't contain alll records of orgininal table. it contains new inserted records + some of existing record with updated values.

    Regards,

    Azam.

  • Ok... thanks...

    Does BitBucket's code do it for you or do you still need help with something?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • bitbucket's code worked perfectly. thanks both of you for your help.

    Regards,

    Azam.

  • Starazam a somewhat theoretical question for you.

    What happens, how are you informed if a Store stops selling a product - how do you delete that (if you must) from the "Products" table?

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • I can divide question in two parts.

    1. How i will be informed.

    in this case, tempProducts will contain all the latest rows(newly added/updated rows). there will be no deleted row in tempProducts, while Products table will contain all the old data(old rows,rows those are now deleted)

    2. How I will delete them.

    for insert/update I will use same queries.

    To delete rows from Products Table or Rows those don't exist in tempProduct Table.

    I think this query should work for delete.

    Delete from products Where

    productname in (Select p.productname from products p left outer join tempTable t on p.productname = t.productname AND p.storename = t.storename where WHERE t.productname IS NULL OR t.storename IS NULL) and storename in (Select p.storename from products p left outer join tempTable t on p.productname = t.productname AND p.storename = t.storename where WHERE t.productname IS NULL OR t.storename IS NULL)

  • Modified your T-SQL to be a select rather than a delete for testing purposes only (eliminated the typing error of "where WHERE") and did not get the desired result:

    Here is my test data:

    Temptable contents:

    ProductName StoreName

    ----------- ----------

    Fruit Joes

    Squeezer Joes

    Fruit sams club

    Squeezer Macys

    Nuts Harware is

    Dental Flo sams club

    tootthpast Johns shop

    Fruit DollarS

    Fruit NULL -- product name is in the Products table

    NULL News -- store name is NOT in the Products table

    Products table contents:

    productname storename

    ----------- ----------

    Fruit Joes

    Squeezer Joes

    Fruit sams club

    Squeezer Macys

    Nuts Harware is

    Bolt walgreens

    Results from running T-SQL: In other words this entry would have been deleted from the Products table. Do not think this is what you intended to do.

    Boltwalgreens

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Your answer to Jeff Moden's question

    No, temp table don't contain all records of orgininal table. it contains new inserted records + some of existing record with updated values.

    Does not agree with your later statement

    in this case, tempProducts will contain all the latest rows(newly added/updated rows). there will be no deleted row in tempProducts, while Products table will contain all the old data(old rows,rows those are now deleted)

    If your second statement is correct then Jeff Moden's solution will be the simplest way of handling the data.

    If temp table contains ALL the final information that original table will have once you do all the inserts and updates to it, why not just "swap" tables?

    If you want to clarify which is correct then we can attempt to assist you in deleting items.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 13 posts - 1 through 12 (of 12 total)

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