Update Fields with values from text file 1

[dachaney]

What is the best way to update fields in a table with values from a text file? I have a table containing Part Prices and other info, but the Price is regularly updated and is supplied in a text file format.

Table: Parts
Fields: PartNo, Price, Description, Supplier

TextFile: Prices
One line per part containing Partno, Price

Using SQL7 NT4

Thanks

 

[jymm]

Personally I would be tempted to create a DTS. If you are using SQL 2000, you can select one of the inputs as a CSV, create a SQL step that would simply update the info from the key in the file and go on your merry way. Schedule it to run on a regular basis and just overlay the file when a new pricing schedule comes in from the pricing people.

then you go get donuts and feed the ducks instead of always doing the work - and you still look like a god!

Steps are similar in version 7 --- if you are unframiliar with DTS, let me know - once you have tried it, you will never go back.

 

[cheyney]

just a suggestion for an easy import like this:
instead of starting off with the dts designer, go through the import data wizard. The last step allows you to create a dts package with all the import info you provided. very handy to create a skeleton dts (or, as the case might be, a totally functional one) and you can always go in and tweak in the dts editor.

cheyney

 

[dachaney]

Thanks, I've no problem in creating a DTS to Import (insert/append) the data from the text file, what I'd like to do is update the records which already exist.
Do I have to pull the data into a temporary table first and then use a SQL procedure (as below) to update the records or can I read the prices direct from the text file (CSV format)?

DECLARE @PartNo varchar(40), @Price real
DECLARE Prices_Cursor CURSOR FOR
SELECT PartNo, Price FROM NewPrices

OPEN Prices_Cursor
FETCH NEXT FROM Prices_Cursor
INTO @PartNo, @Price

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Parts SET [PartPrice]=@Price WHERE [PartNo] = @PartNo
FETCH NEXT FROM Prices_Cursor
INTO @PartNo, @Price
END
CLOSE Prices_Cursor
DEALLOCATE Prices_Cursor

Is there an easier/quicker way to achive this?


Thanks

 

[jymm]

Unless it is an extreem situation, I prefer not using cursors (as do most SQL admins that have been through the MS classes). If this is all you want to do, I would be tempted to pull the entire csv into a new table then run a sql query that would update only the existing records.

Are there ever new items in the input file or items that no longer exist??? In other words do you need to consider Insert, Update, Delete logic??? Then you could pull the entire file into a 'new' table (clear it out first) then three queries that would update the matches, add the new ones & delete the old ones is in order.

whatcha think?

 

[dachaney]

All I'm trying to do is Update existing records.

I've set up a DTS package that will pull the entire CSV file into a table and then run the SQL proc as shown to Update the existing records.

If Cursors aren't the best method how can I achieve the update?

 

[tlbroadbent]

Update Parts
Set Price = NewPrices.Price
From Parts
Join NewPrices
On Parts.PartNo=NewPrices.PartNo Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[jymm]

DRAT! --- Terry beat me... ah well.

 

[dachaney]

Thanks, simple really!

 

[SQLSister]

You do need to really make sure you don't need inserts and deletes too. We've never gotten a price update that didn't eliminate some parts or add others. Just to make sure this isn't the case run these queries:

To check for new records
Select * from NewPrices
left Outer Join Parts
On NewPrices.PartNo = Parts.PartNo
Where Parts.PartNo is null

To check for records deleted
Select * from Parts
left Outer Join NewPrices
On Parts.PartNo = NewPrices.PartNo
Where NewPrices.PartNo is null

 

[dachaney]

In this case it is definitely just updates, but I do have other tables which need Inserts as well, so your comments are helpful. Thanks