July 30, 2018 at 4:34 am
hello 🙂
I'm new to MS SQL Servers and I have to update the old prices in the table with the new prices from a csv file.
the old method was by using openrowset + bulk + a format file - is somewhere a user friendly documentation or a blog entry for understanding these commands?
of course there are some new wishes so I would like to understand exactly how it is working - thank you very much 🙂
July 30, 2018 at 8:29 am
Look up BULK INSERT, with the idea of using it to populate a "staging" table that will hold the data, and then you can use UPDATE where you JOIN that staging table to the existing price table and perform the update. You will want to use a TRANSACTION for this update.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 30, 2018 at 8:40 am
One option could be to use SSIS. It depends if this is a one off process or something that will need to happen on a regular basis. SSIS will also allow any transformation if required.
Here is a link to a good piece of step by step advice for loading a file this way.
https://blog.sqlauthority.com/2011/05/12/sql-server-import-csv-file-into-database-table-using-ssis/
I also agree about using a staging table as an interim step before doing the update.
July 31, 2018 at 12:24 am
thank you for your answers 🙂
I have a lot to learn.
I think I have to do it once a month, but maybe twice a month - the problem is that I have different vendors and their lists of product items aren't the same every month, like there a 3 columns more this month, but usally we do not need the 3 columns more.
===
For the moment we are importing ~3000 products, of course this amount is in the list(csv - file) and we don't want to add new products (in the database), so I have to check is the product in our table - if yes and the price amount is not null then update price and so on.
The it man programming the last import means it is about 8 hours to import this amount - is this a realistic time for 3k products? - he also updates only the price - now the shop man has some wishes I have to build in the script.
the schematic would be (hope you can help me to find the perfect smooth way to do this 🙂 )
if (productnumber = in database){
if (vendorGK = in database && csvprice is not null){
update price and other things (like dimensions)
} ifelse (vendorGU = in database){
insert vendorGK new in database
}else{
nothing
}
} else
nothing
}
=======
if the vendor has 3 columns for x, y, z (lenght, width, height) and we have 1 column (dimensions) how can I put the 3 values together without a problem?
is it possible to convert the price from gramm to kilogramm? (how would you do - take the whole csv into a new table and add a new column kg and insert the weight from gramm / 1000)
what if the productnumber and the vendors selling this product are in different tables - and I have to update the price and maybe that the product is now buyable from vendorGK?
and last but not least - can I convert manufacturer like 'VARTA' to a speciefied number - we match articles-producers by ids but we get them now in text - so I would have to check
if 1 -> varta
elseif 2 -> panasonic
elseif .. -> ..
else -> do nothing
=======
thank you very very much for your help 🙂
July 31, 2018 at 12:47 am
If you want to use SSIS, you need a consistent dataset. If you can't do that, it's going to be a real fight to try and get it to work. If your dataset had 3 new columns every month, and your vendor won't change that then I suggest using BULK INSERT.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 1, 2018 at 8:30 am
drakul - Tuesday, July 31, 2018 12:24 AMthank you for your answers 🙂I have a lot to learn.
I think I have to do it once a month, but maybe twice a month - the problem is that I have different vendors and their lists of product items aren't the same every month, like there a 3 columns more this month, but usally we do not need the 3 columns more.
===
For the moment we are importing ~3000 products, of course this amount is in the list(csv - file) and we don't want to add new products (in the database), so I have to check is the product in our table - if yes and the price amount is not null then update price and so on.
The it man programming the last import means it is about 8 hours to import this amount - is this a realistic time for 3k products? - he also updates only the price - now the shop man has some wishes I have to build in the script.
the schematic would be (hope you can help me to find the perfect smooth way to do this 🙂 )
if (productnumber = in database){
if (vendorGK = in database && csvprice is not null){
update price and other things (like dimensions)
} ifelse (vendorGU = in database){
insert vendorGK new in database
}else{
nothing
}
} else
nothing
}=======
if the vendor has 3 columns for x, y, z (lenght, width, height) and we have 1 column (dimensions) how can I put the 3 values together without a problem?
is it possible to convert the price from gramm to kilogramm? (how would you do - take the whole csv into a new table and add a new column kg and insert the weight from gramm / 1000)
what if the productnumber and the vendors selling this product are in different tables - and I have to update the price and maybe that the product is now buyable from vendorGK?
and last but not least - can I convert manufacturer like 'VARTA' to a speciefied number - we match articles-producers by ids but we get them now in text - so I would have to check
if 1 -> varta
elseif 2 -> panasonic
elseif .. -> ..
else -> do nothing=======
thank you very very much for your help 🙂
Additionally, you might want to use a new column and call it UOM, or UnitOfMeasure. Converting prices to per gram from per kilogram, or vice versa, might imply to those purchasing that they can specify a smaller quantity in a purchase order than the vendor might allow. Always keep prices in the "unit of measure" that the vendor sells the product in, and then use that as part of all the pricing calculations, e.g. Number of Units purchased times price per unit. If the vendor uses a separate part number for different units of measure for the same item (e.g. six pack vs. 12 pack), then it's easy to handle, but you have to be alert for the possibility that some vendors may need both a part number AND a unit of measure, so you probably need a table to store the different available units of measure for a given part for a given vendor / supplier.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply