| Author |
Topic |
|
token
Posting Yak Master
133 Posts |
 Posted - 2006-04-16 : 08:33:48
|
| Hi all,Not sure if anyone has tried this before but I want to compare the contents of 3 flat-file data sources BEFORE I insert the data into my table.I have 3 files.... A B C. They all contain similar data on products. But what I want to do is create some method where SQL Server will compare the Product code (same in each data source file) and price for all 3 files and only insert a row where the price is the cheapest.So file A might have Product Code "X" with a price of $50. But file B has Product Code "X" with a price of $20. So I want to insert the row from file B. And so on like this until all 3 files have had their data compared and I know that the products with the cheapest price are in my table.Any ideas? I looked at the Conditional Split function but I'm not sure what to do. In my mind I am thinking I would link all 3 files to one Conditional Split, have it process, and then insert to OLEDB destination. But this is not possible. You can only have one Conditional Split per data source file.Cheers guys! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-16 : 08:57:35
|
| Why not insert all three file into staging tables (or a single staging table) then insert the cheapest from there.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
token
Posting Yak Master
133 Posts |
Posted - 2006-04-16 : 09:34:22
|
| not a bad idea at all. im a newbie at SQL Server so i cant think that far ahead!!I have another problem which seems easy to fix, but i dont know what do again. Basically if the "Stock" column in the data source is less than 1, then I want to replace that value with the number '1' before it is inserted into my Products table. If the "Stock" column is greater than or equal to 1, then I want to replace that value with the number '2' before it is inserted into my Products table.Why am I replacing the values with the numbers '1' and '2' I hear you ask. This is because my Products table references another table called Availability, where the identity number '1' means "Awaiting Delivery" and identity number '2' means "In Stock".How do I replace values before they are inserted into a table? I am using SQL Server 2005 with the Business Intelligence software that lets you drag and drop functions such as Conditional Split (BIDS). Its almost the same as DTS in SQL Server 2000 I think. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-16 : 10:01:57
|
| Again if you use staging tables this becomes trivial.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
token
Posting Yak Master
133 Posts |
Posted - 2006-04-16 : 10:16:00
|
| Could you tell me more about what to do?So I create a staging/dummy table, put the data in, and then use an SQL statement to replace values? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-04-16 : 10:22:29
|
quote:
Originally posted by token
Could you tell me more about what to do?So I create a staging/dummy table, put the data in, and then use an SQL statement to replace values?
1. create staging table2. import to staging table3. perform the update.assuming the stock column is integerupdate yourtable set stock = case when stock < 1 then 1 when stock >= 1 then 2 end
KH |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-16 : 10:28:29
|
| You import everything into a staging table (or staging tables) then run a stored procedure to move the data to the production table. That stored procedure might be a single insert statement but could be more complicated. It would select the cheapest value and also replace the values.something likeinsert prodtbl (prodcode, price, Stock)select prodcode, price, case when Stock = 0 then 1 else 2 endfrom ProdStaging pwhere not exists (select * from ProdStaging p2 where p2.prodcode = p.prodcode and p2.price < p.price)What if the cheapest has stock = 0 but a more expensive is in stock?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
token
Posting Yak Master
133 Posts |
Posted - 2006-04-16 : 12:15:28
|
| > What if the cheapest has stock = 0 but a more expensive is in stock?Good question, something I thought about as well. Perhaps I should just display all products and their prices on the website and let the customer choose. So if they want a product immediately, they can pay more. If they want to wait for a cheaper one, they can.What do you think is good? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-16 : 12:31:34
|
| It depends on what you want.You could display those that are in stock but just say that you are expecting cheaper one's in x days.Or you could let the price fluctuate depending on what's in stock. You have to decide what the customers would prefer.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
token
Posting Yak Master
133 Posts |
Posted - 2006-04-16 : 12:37:02
|
| Right... so now I could have potentially have 3 different prices for each product. So I would need to know which Supplier it will come from.I have a column in my Products table called "SupplierID". It would be great if all rows inserted from flat-file A inserted number '1' for SupplierID in the products table. The same process would apply for flat-file B and C.Any idea how to achieve this? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-16 : 12:46:47
|
| Sounds like you need three stagig tables for the three files. Then when you insert to the production table just hard code the supplier id depending on which table the data comes from.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
token
Posting Yak Master
133 Posts |
Posted - 2006-04-16 : 12:55:45
|
| Is there any way to do this at the point of reading the flat-file in thereby excluding the need for a stage table?So in my Data Flow screen it would be something like DATA SOURCE A -> ADD COLUMN AND POPULATE WITH VALUE '1' -> DESTINATION OLEDB |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-16 : 14:34:03
|
| Use a derived column transformation to add another column to the load with a constant value.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
token
Posting Yak Master
133 Posts |
Posted - 2006-04-16 : 15:11:39
|
| Excellent!!! Thanks for that nr, it works nicely now. Final obstance, how to populate the destination with unique identifiers for the primary key? At the moment, I cant populate my table from the flat-file data source if my table requires a primary key. It returns an error saying "NULL value cannot be inserted" or something like that. But if I remove the primary key, then the insertions happen just fine.Is there any way to create primary key values after insertion? It has to be after insertion or else BIDS could try to place replicate unique identifiers for each flat-file data source. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-16 : 15:31:29
|
| you can add an identity after the insert viaalter table tbl add tbl_id int identity (1,1)but will have to drop it again before the next insert.Better to create a view on the table which excludes the identity then insert into that view.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
token
Posting Yak Master
133 Posts |
Posted - 2006-04-17 : 11:00:01
|
| Wow, this is all working perfectly. many thanks to nr! The final and last question. Just before it inserts into my OLEDB Destination, I want it to filter out records so that only those with a ManufacturerName of "CANON" or "HP" will be inserted. I tried to implement this using the expression [ProductName] == "CANON" || "HP" but I get an error saying===Error at Data Flow Task [Conditional Split [500]]: Attempt to set the result type of binary operation "ProductName == "'CANON" || "HP"" failed with error code 0xC0047080.Error at Data Flow Task [Conditional Split [500]]: Computing the expression "ProductName == "'CANON" || "HP"" failed with error code 0xC0047084. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.===However if I just type [ProductName] == "CANON" as the expression then it works just fine and only those records with CANON as the ProductName/Manufacturer are inserted.Any idea how to get the OR statement to work? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-17 : 14:23:22
|
| tryProductName == "CANON" || ProductName == "HP"==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Compaing 3 flat file sources before insertion