Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Condional Update/insert from one table to other. Expand / Collapse
Author
Message
Posted Friday, June 20, 2008 3:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 2:10 AM
Points: 10, Visits: 33
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.
Post #521086
Posted Friday, June 20, 2008 3:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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

Before posting a performance problem please read
Post #521091
Posted Friday, June 20, 2008 4:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 2:10 AM
Points: 10, Visits: 33
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.
Post #521109
Posted Friday, June 20, 2008 5:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 2:10 AM
Points: 10, Visits: 33
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.
Post #521120
Posted Saturday, June 21, 2008 12:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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

Before posting a performance problem please read
Post #521287
Posted Saturday, June 21, 2008 4:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 35,371, Visits: 31,917
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #521334
Posted Sunday, June 22, 2008 4:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 2:10 AM
Points: 10, Visits: 33
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.
Post #521392
Posted Sunday, June 22, 2008 8:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 35,371, Visits: 31,917
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #521410
Posted Sunday, June 22, 2008 9:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 2:10 AM
Points: 10, Visits: 33
bitbucket's code worked perfectly. thanks both of you for your help.

Regards,
Azam.
Post #521415
Posted Sunday, June 22, 2008 9:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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

Before posting a performance problem please read
Post #521418
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse