How to import and update a column at the same time?
-
Hi,
I've got a task from the management that I really don't know how to solve. Hope someone here can point me in the right direction.
I'm strictly a dba and don't know much about writing advanced queries in t-sql. I'm on SQL2000.
This is what I've got today.
A db called "PriceDump" with a table called "PricedumpData".
Every night we get a pipe "|" separated txt file containing data from a price database from a third party. This txt file contains the following columns:
Company|Departure|Country|Destination|DepartureDate|ReturnDate|UpdateDate|DepTime|RetTime|Hotel|Link|Roomtype|Classification|Duration|Price|Currency|Link2
Today I import this file to the "PricedumpData" table just like it is, so obviously the table contains the same coulmns.
The management use this table with Excel to make pivot tables looking at price changes for products over a certain time.
Now, the management want me to add a column in the table called "CurrentPrice", this column should be set ,while importing new data, to either "YES" or "NO" depending on this criteria:
Does this product already excist in the table? If it does, has the price for the product changed since last import? If it has, set column "CurrentPrice" to "YES".
If it does not already excist (it's a new product), set column "CurrentPrice" to "YES".
If it does excist but the price hasn't changed, set column "CurrentPrice" to "NO".
A "product" is defined by the columns: Company,Departure,Country,Destination,DepartureDate,Hotel,Roomtype,Duration. These columns together makes a product unique.
The excisting data in the "PricedumpData" table isn't a problem cause I've told the management that it might have to be truncated to get this to work.
I was thinking that maybe I could use a temp table that I could do the txt file import to and then use cursors or a while loop to import it row-by-row to the "PricedumpData" table
and do the "CurrentPrice" update at the same time?
But as I said, I''ve not got a clue about how to do this. So if anyone here has got some idea about how to solve this it would be very much appreciated.
Thanks!
/vipw
-
it's not hard at all, as long as there is something that uniquely identifies each product. My example below only uses two columns, you would need to use all Company,Departure,Country,Destination,DepartureDate,Hotel,Roomtype,Duration
you need to import the file as a separate table from the PricedumpData.
to get what is "new", and does not already exist, you'll end up doing a full join on the existing table, and a left join for items that changed.
here's an example, and I'm assuming for complexity that TWO columns together make can uniquely identify the row:
drop table PriceDumpStage
create table PriceDumpData (
SKU int,
SKU2 int,
productName varchar(30),
CurrentPrice money)
INSERT INTO PriceDumpData(SKU,SKU2,ProductName,CurrentPrice) VALUES(1,1,'widgets',4.99)
INSERT INTO PriceDumpData(SKU,SKU2,ProductName,CurrentPrice) VALUES(2,3,'thingamagigs',4.99)
INSERT INTO PriceDumpData(SKU,SKU2,ProductName,CurrentPrice) VALUES(4,3,'turnblats',4.99)
INSERT INTO PriceDumpData(SKU,SKU2,ProductName,CurrentPrice) VALUES(2,4,'cornfunders',4.99)
create table PriceDumpStage (
SKU int,
SKU2 int,
productName varchar(30),
CurrentPrice money)
INSERT INTO PriceDumpStage(SKU,SKU2,ProductName,CurrentPrice) VALUES(2,3,'thingamagigs',5.50)
INSERT INTO PriceDumpStage(SKU,SKU2,ProductName,CurrentPrice) VALUES(4,56,'hooksterns',7.00)
INSERT INTO PriceDumpStage(SKU,SKU2,ProductName,CurrentPrice) VALUES(2,4,'cornfunders',4.77)
--not in PriceDumpData=new
SELECT
PriceDumpData.*,
PriceDumpStage.*
FROM
PriceDumpData
FULL OUTER JOIN
PriceDumpStage ON PriceDumpData.SKU=PriceDumpStage.SKU AND PriceDumpData.SKU2=PriceDumpStage.SKU2
WHERE PriceDumpData.ProductName IS NULL
results:
NULL NULL NULL NULL 4 56 hooksterns $ 7.00 --in both tables AND price changed ---ignore same price rows
SELECT
PriceDumpData.*,
PriceDumpStage.*
FROM
PriceDumpData
LEFT OUTER JOIN
PriceDumpStage ON PriceDumpData.SKU=PriceDumpStage.SKU AND PriceDumpData.SKU2=PriceDumpStage.SKU2
WHERE PriceDumpData.CurrentPrice <> PriceDumpStage.CurrentPrice
results:
2 3 thingamagigs $ 4.99 2 3 thingamagigs $ 5.50 2 4 cornfunders $ 4.99 2 4 cornfunders $ 4.77 Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
here is the same logic, but with the actual columns you identified: assuming you import the text file into the staging table PriceDumpStage , this will work:
SELECT
PriceDumpData.*,
PriceDumpStage.*
FROM
PriceDumpData
FULL OUTER JOIN
PriceDumpStage
ON PriceDumpData.Company =PriceDumpStage.Company
AND PriceDumpData.Departure =PriceDumpStage.Departure
AND PriceDumpData.Country =PriceDumpStage.Country
AND PriceDumpData.Destination =PriceDumpStage.Destination
AND PriceDumpData.DepartureDate =PriceDumpStage.DepartureDate
AND PriceDumpData.Hotel =PriceDumpStage.Hotel
AND PriceDumpData.Roomtype =PriceDumpStage.Roomtype
AND PriceDumpData.Duration =PriceDumpStage.Duration
WHERE PriceDumpData.Company IS NULL
ELECT
PriceDumpData.*,
PriceDumpStage.*
FROM
PriceDumpData
LEFT OUTER JOIN
PriceDumpStage
ON PriceDumpData.Company =PriceDumpStage.Company
AND PriceDumpData.Departure =PriceDumpStage.Departure
AND PriceDumpData.Country =PriceDumpStage.Country
AND PriceDumpData.Destination =PriceDumpStage.Destination
AND PriceDumpData.DepartureDate =PriceDumpStage.DepartureDate
AND PriceDumpData.Hotel =PriceDumpStage.Hotel
AND PriceDumpData.Roomtype =PriceDumpStage.Roomtype
AND PriceDumpData.Duration =PriceDumpStage.Duration
WHERE PriceDumpData.Price <> PriceDumpStage.Price
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you very much Lowell.
I will try this and report back, thanks again!
/vipw
-
man i don't seem to read everything early in the morning:
here is how to INSERT the new items from the staging table:
INSERT INTO PriceDumpData(Company,Departure,Country,Destination,DepartureDate,ReturnDate,UpdateDate,DepTime,RetTime,Hotel,Link,Roomtype,Classification,Duration,Price,Currency,Link2)
SELECT
PriceDumpStage.Company,
PriceDumpStage.Departure,
PriceDumpStage.Country,
PriceDumpStage.Destination,
PriceDumpStage.DepartureDate,
PriceDumpStage.ReturnDate,
PriceDumpStage.UpdateDate,
PriceDumpStage.DepTime,
PriceDumpStage.RetTime,
PriceDumpStage.Hotel,
PriceDumpStage.Link,
PriceDumpStage.Roomtype,
PriceDumpStage.Classification,
PriceDumpStage.Duration,
PriceDumpStage.Price,
PriceDumpStage.Currency,
PriceDumpStage.Link2
FROM
PriceDumpData
FULL OUTER JOIN
PriceDumpStage
ON PriceDumpData.Company =PriceDumpStage.Company
AND PriceDumpData.Departure =PriceDumpStage.Departure
AND PriceDumpData.Country =PriceDumpStage.Country
AND PriceDumpData.Destination =PriceDumpStage.Destination
AND PriceDumpData.DepartureDate =PriceDumpStage.DepartureDate
AND PriceDumpData.Hotel =PriceDumpStage.Hotel
AND PriceDumpData.Roomtype =PriceDumpStage.Roomtype
AND PriceDumpData.Duration =PriceDumpStage.Duration
WHERE PriceDumpData.Company IS NULL
here is how to UPDATE the existing items from the staging table:
UPDATE PriceDumpData
SET PriceDumpData.Price = PriceDumpStage.Price
FROM
PriceDumpStage
WHERE PriceDumpData.Company =PriceDumpStage.Company
AND PriceDumpData.Departure =PriceDumpStage.Departure
AND PriceDumpData.Country =PriceDumpStage.Country
AND PriceDumpData.Destination =PriceDumpStage.Destination
AND PriceDumpData.DepartureDate =PriceDumpStage.DepartureDate
AND PriceDumpData.Hotel =PriceDumpStage.Hotel
AND PriceDumpData.Roomtype =PriceDumpStage.Roomtype
AND PriceDumpData.Duration =PriceDumpStage.Duration
AND PriceDumpData.Price <> PriceDumpStage.Price
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply