Update failing from CSV if data not in table

Question

Post reply

Update failing from CSV if data not in table

pauld 57577

Valued Member

Points: 68
More actions
August 20, 2018 at 5:57 pm

#367389

First post here guys so sorry if Im in the wrong place for this. Its been the bain of my life for the last 2 days and my SQL experience is not huge so apologies if this is a simple one
Ok so I have a csv file with pid, description, price,
I have a sql table with pid, description, price
The csv file may contain additional rows that are not in the db and I want to just ignore them and update all other rows in the db that do exist.
Now the state
UPDATE a SET pWholesalePrice=src.NZToner, pListPrice=src.Sell, pPrice=(NZToner * 1.35)
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'text;Database=C:\data\;', 'SELECT * FROM [Test1.csv]') as src INNER JOIN Products a ON a.pID=TRY_CONVERT(nvarchar, src.Stockcode)
This works if all the pids are in the db.
soDB
PID Desc Price
123 Something 10
CSV
PID Desc Price
123 Something 10
If the csv file has any additional pids it does not update anything. Returns 0 rows updated
PID Desc Price
123 Something 10
321 Something Else 20 (just ignore this and move on with updating what is there)
Could someone please advice how to update the rows in the database and ignore if the csv has additional.
So much thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MinhL7 Mr or Mrs. 500 Points: 529 More actions · Answer 1

Hi you can do something like this.
DECLARE @CT INT SET @CT = (SELECT COUNT([pWholesalePrice]) FROM [dbo].[Products])

IF ISNULL(@Ct,0) > 0 BEGIN --update END ELSE BEGIN --do something END

pauld 57577 Valued Member Points: 68 More actions · Answer 2

Modified Code
DECLARE @CT INT SET @CT = (SELECT COUNT([pID]) FROM [dbo].[Products])

IF ISNULL(@Ct,0) > 0 BEGIN UPDATE a SET pWholesalePrice=src.NZToner, pListPrice=src.Sell, pPrice=(NZToner * 1.35) FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'text;Database=C:\data\;', 'SELECT * FROM [Test1.csv]') as src INNER JOIN Products a ON a.pID=TRY_CONVERT(nvarchar, src.Stockcode) END ELSE BEGIN SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END

Again results in 0 rows affected. If I remove the 2 products from the csv file that is not in the db it works.. Just to be clear Im not looking to add the lines from the csv. Just want to update the db from the csv with products that are already there

andycadley SSCertifiable Points: 5296 More actions · Answer 3

An UPDATE statement will only ever change existing rows, not add them. If you want to both update and insert rows, you need to use MERGE.

pauld 57577 Valued Member Points: 68 More actions · Answer 4

Just to be clear Im not looking to add the lines from the csv. Just want to update the existing db from the csv with products that are already in the database

andycadley SSCertifiable Points: 5296 More actions · Answer 5

Then I can't see anything obviously wrong with your query, have you checked what the results of the OPENROWSET are? Is it possible the file isn't being read correctly?

FWIW, doing basically the same thing here but using the bulk option with a format file works:

Drop Table If Exists #Products Create Table #Products ( Pid int, [Desc] VarChar(100), Price int )Insert Into #Products Values(123,'blah',12)
Update P
Set
P.[Desc] = ORS.[Desc],
P.Price = ORS.Price
From OpenRowset(Bulk 'C:\temp\test1.csv', FormatFile='c:\temp\format.fmt') ORS
Join #Products P On ORS.Pid = P.Pid
Select * From #Products

Where format.fmt contains:
9.0 3 1 SQLCHAR 0 0 "," 1 "Pid" "" 2 SQLCHAR 0 0 "," 2 "Desc" "" 3 SQLCHAR 0 0 "\r\n" 3 "Price" ""

So I don't know if it's a quirk in the ACE driver or something in your DDL that we can't see.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

You have TRY_CONVERT(nvarchar, src.Stockcode) - verify the column you are trying to match on is defined as nvarchar and the specific length. If the column is defined as nvarchar(10) then make sure you specify TRY_CONVERT(nvarchar(10), src.StockCode).

Not sure this is causing the problem - but you should always specify the character length for varchar/char/nvarchar/nchar declarations.

You could also try separating the statements as:

WITH inputdata
AS (
SELECT *, pID = TRY_CONVERT(nvarchar(10), src.SourceCode)
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'text;Database=C:\data\;', 'SELECT * FROM [Test1.csv]') as src
)
UPDATE a
SET ...
FROM Products a
INNER JOIN inputdata i ON i.pID = a.pID

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 7

when updating information based on an external source be it CSV, BCP or whatever I always like to keep an audit trail of say last 6 months of updates.

so my approach would be

create a permanent table with added fields processdate, processedflag
Bulk insert my CSV file, set processeddate and Flag (default 'N')
processedflag values i use
N not processed
R Rejected failed validation (used for later investigation)
P to process
Y Processed
Run validation
Process records that pass validation

periodically i purge data older than N months,

***The first step is always the hardest *******