Condional Update/insert from one table to other.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Condional Update/insert from one table to...

13 posts, Page 1 of 2

1 2 »»

Condional Update/insert from one table to other.

Rate Topic

Display Mode

Topic Options

Author

Message

starazam

Posted Friday, June 20, 2008 3:38 PM

Grasshopper

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

bitbucket-25253

Posted Friday, June 20, 2008 3:55 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 5,103, Visits: 20,220

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

starazam

Posted Friday, June 20, 2008 4:44 PM

Grasshopper

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

starazam

Posted Friday, June 20, 2008 5:25 PM

Grasshopper

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

bitbucket-25253

Posted Saturday, June 21, 2008 12:18 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 5,103, Visits: 20,220

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

Jeff Moden

Posted Saturday, June 21, 2008 4:07 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 PM
Points: 32,931, Visits: 26,820

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #521334

starazam

Posted Sunday, June 22, 2008 4:12 AM

Grasshopper

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

Jeff Moden

Posted Sunday, June 22, 2008 8:56 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 PM
Points: 32,931, Visits: 26,820

Ok... thanks...

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

--Jeff Moden

Post #521410

starazam

Posted Sunday, June 22, 2008 9:20 AM

Grasshopper

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

bitbucket-25253

Posted Sunday, June 22, 2008 9:35 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 5,103, Visits: 20,220

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 »

13 posts, Page 1 of 2

1 2 »»

Permissions