June 20, 2008 at 3:38 pm
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.
June 20, 2008 at 3:55 pm
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.
June 20, 2008 at 4:44 pm
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.
June 20, 2008 at 5:25 pm
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.
June 21, 2008 at 12:18 pm
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.
June 21, 2008 at 4:07 pm
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
Change is inevitable... Change for the better is not.
June 22, 2008 at 4:12 am
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.
June 22, 2008 at 8:56 am
Ok... thanks...
Does BitBucket's code do it for you or do you still need help with something?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2008 at 9:20 am
bitbucket's code worked perfectly. thanks both of you for your help.
Regards,
Azam.
June 22, 2008 at 9:35 am
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?
June 22, 2008 at 10:29 am
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)
June 22, 2008 at 11:50 am
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
June 22, 2008 at 7:23 pm
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.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply