SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Condional Update/insert from one table to other.


Condional Update/insert from one table to other.

Author
Message
starazam
starazam
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7901 Visits: 25280
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
starazam
starazam
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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.
starazam
starazam
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7901 Visits: 25280
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87374 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
starazam
starazam
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87374 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
starazam
starazam
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 33
bitbucket's code worked perfectly. thanks both of you for your help.

Regards,
Azam.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7901 Visits: 25280
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search