text document to sql

[hlybbi]

i have got an txt file with products and pricing that i will recieve every week and i need to compare this products in the txt file with my sql products, what i have done is to extraxt the txt file into a new table in my sql, what would be the best way to compare the new records with the old ones, any sucestions

Best regards Hlynur

http://www.xodus.net

 

[TheBugSlayer]

Hmmm...let's see, a cursor is one possible solution. It can be slow, especially if you have a lot or records. You could open a cursor for the table you imported from the text file. Then for each record locate its counterpart in the master table; if it exists compare the fields one by one and take the proper steps in each case (found or not).

I am thinking still...

 

[philhege]

What exactly do you mean by "compare"? What changed? Does the file contain all of the information necessary for a product, or is the structure of the uploaded file different from the product table structure?

Here's a simple example:

select a.productID, (a.price-b.price) as price_delta
FROM producttable a
JOIN productupdate b on b.productID = a.productID
WHERE a.price-b.price <> 0

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[hlybbi]

i need to compare if the current record exist, and if it exist i need to uppdate the record and if it doesint exist i need to insert the new record

Best regards Hlynur

http://www.xodus.net

 

[philhege]

Assuming that your key field is, say, productID, then


SELECT * FROM Productupdate
WHERE ProductID NOT IN(SELECT productID from Producttable)

Taking this a step further, if you have no autonumber field,

INSERT INTO Producttable
SELECT * FROM Productupdate
WHERE ProductID NOT IN(SELECT productID from Producttable)

If you do have an autonumber field, enumerate the field names and omit the autonumber field in the INSERT statement.

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[SQLSister]

First you update existing records, then you insert records which do not match any existing records.

UPDATE table1
SET field1 = table2.field1,
field2 = table2.field2,
field3 = table2.field3
FROM table1
INNER JOIN table2
ON table1.id = table2.id

INSERT INTO table1 (field1, field2, field3)
SELECT field1, field2, field3 FROM table2 LEFT JOIN table1
ON table1.id = table2.id
WHERE table1.id is null

Questions about posting. See faq183-874

 

[hlybbi]

nice i dont have to do anything my self
thx alot SQLSister this works

Best regards Hlynur

http://www.xodus.net

 

[AppDev76]

SQL Sister,
I have a similar case and I was wondering if you have seen any performance difference using the NOT IN(SELECT...) or joiing the tables with a WHERE condition of table.PK IS NULL?

Thanks

 

[SQLSister]

Joining is generally better for performance. Not in does not use the indexes properly.

Questions about posting. See faq183-874