pretty new to dts and not even sure I'm standing in the right light if you know what I mean?

I have a csv file that comes down twice daily. I need to import into a table, updating rows (products) that exist in the file, and remove rows that do not. I have managed to import, but it appends to the bottom of the table.

1) am i on the right track with dts?
2) What is the best way of approaching this?

Thanks in advance

Hello,

Importing in this manner you have several options:

Append to the current table
Remove existing rows and insert new data
..etc..

If you have to update current rows or do some custom action, where you are comparing rows between the source and destination, you may want to import into a holding table, and do the compare between the two (either through straight T-SQL in an execute task step, or do vbscript to do a compare in an activeX task).

What specifically do you want to do, as I wasn't completely sure?

Brian

I have one sql table, to be populated twice a day from data contained within a csv file. Some data within the csv file will be new and some will be updates.

So I need to insert the new, update the existing and then remove the old (doesnt exist in the csv) from the sql table.

I Managed to create a package that deletes all the rows in the sql table, and then import the new text file again, it takes about ten seconds to complete, but that obviously means the tables are empty.. and the website offline with no data.

Heres what I'm thinking, I add a date column to the table with default of (getdate()) import the first lot of data to populate the rows, that will have the date and time for this import, then insert all the new data, and remove the old based on date and time.

The downside to this is again of course.... duplicate data! Argh!

1. Import the CSV into a Temporary table
2. Delete all the rows in the production table NOT IN Temporary table (compare primary keys)
3. UPDATE all rows in production table that are also in Temporary Table (Compare primary keys)
4. INSERT all rows in Production table that are IN temporary table but not Production table

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

Right have done that and manged to get the delete and update queries working great! :-) But I just REALLY do not know where to start with the Insert, In fact I feel really let down with the wrox books on this subject... maybe I'm not looking in the right places........

I need to compare the two tables (tbl_Products and tbl_ProductsTemp) and insert the records that dont exist.

ProdCode is my Key
ShortDesc, LongDesc, Stock are three of the fields.

Head full, fingers sore.... football about to start.... and must complete this tomorrow.... Help! Thanks in advance for your patience and assistance... V.Tired Stuart.

Hey,

If you use a vbscript task, you can use the following queries, and test where the p.ProdCode is NULL in the recordset:

insert into tbl_Products
(
ProdCode, ShortDesc, LongDesc, Stock
)
select t.ProdCode, t.ShortDesc, t.LongDesc, t.Stock, p.ProdCode from tbl_ProductsTemp t
left outer join tbl_Products p
on t.ProdCode = p.ProdCode
and t.ShortDesc = p.ShortDesc
and t.LongDesc = p.LongDesc
and t.Stock = p.Stock

Otherwise, the only other way that I can think of doing it is:

insert into tbl_Products
(
ProdCode, ShortDesc, LongDesc, Stock
)
select t.ProdCode, t.ShortDesc, t.LongDesc, t.Stock from tbl_ProductsTemp t
where t.ProdCode + '/' + t.ShortDesc + '/' + t.LongDesc + '/' + t.Stock not in
(
select p.ProdCode + '/' + p.ShortDesc + '/' + p.LongDesc + '/' + p.Stock from tbl_Products p
)

This concatenates the entire string to check it's uniqueness.

Brian

Hey,

By the way, the second option can be used in a execute task task, not in a vbscript task. The first one must be used in a vbscript task using ADO, because you can't say "where p.ProdCode is NULL" because it's really not null, it just doesn't have a value.

Just thought I would clear that up.

Brian

O' how I have tried to get the second option working... and o' how I have failed. Heres what I have so far... which works, but of course does not check if the record exists... so is full of duplicates after this has run.

INSERT
INTO tbl_Products
                      (tbl_Products.ProductCatergory,
tbl_Products.ProductSubCatergory, tbl_Products.ProductCreation,
tbl_Products.ProductManufacturer,
                      tbl_Products.ProductCode,
tbl_Products.ProductShortDesc, tbl_Products.ProductLongDesc,
tbl_Products.ProductImage, tbl_Products.ProductStock,

                  tbl_Products.ProductETA, tbl_Products.ProductRRP,
tbl_Products.ProductPrice)
SELECT tbl_ProductsTemp.ProductCatergory,
tbl_ProductsTemp.ProductSubCatergory, tbl_ProductsTemp.ProductCreation,

                      tbl_ProductsTemp.ProductManufacturer,
tbl_ProductsTemp.ProductCode, tbl_ProductsTemp.ProductShortDesc,
tbl_ProductsTemp.ProductLongDesc,
                      tbl_ProductsTemp.ProductImage,
tbl_ProductsTemp.ProductStock, tbl_ProductsTemp.ProductETA,
tbl_ProductsTemp.ProductRRP,
                      tbl_ProductsTemp.ProductPrice
FROM tbl_ProductsTemp LEFT OUTER
JOIN
                      tbl_Products ON tbl_Products.ProductCode
= tbl_ProductsTemp.ProductCode

Think I sorted it...

WHERE tbl_ProductsTemp.ProductCode NOT IN
  (SELECT ProductCode FROM tbl_Products)

Any reason why this would be incorrect?

Hello,

That looks like a valid statement...

Brian