Updating Pricelist into our Database

[SBSP]

Currently users are manually typing the price into their order when capturing it.

Our system has a price list functionality, but its a mission to get the prices in as you have to
do it one by one and its import feature is a bit complicated.It has to be precicley right with a whole bunch of fields and values which is not understood/used by the user.

So I want to make a VBA macro that will take the data from their existing Excel Price list then push that into the database which will allow them to only enter the Item Code, Price List code and Obviously the price.
Actually all of the info is already on the excel Price List

I have 2 ways of doing this. (We use MS SQL2008R2 by the way)

1.Clean the price list on the database side first
as in "Delete from table where pricelist = 'pricelist code'"

2A. Then take each item on the price list in a loop from row 1 down to the row last row before the empty rows start
and run a query on each item to insert the item into the database.

2B. Or create a Stored Procedure and make use of parameters @ItemCode @PriceList, and @UnitPrice to insert on each Loop "Step" or count.

My Concerns
I'm basically sending data into the database via the back end and not the ERP systems Front end.
Would you say its a bad idea doing this ? The system is Called Accpac ERP 500. v5.5
Its got an integrity checker so I THINK i have all the correct tables ( ICPRIC & ICPRICP)
I have tested this on a backup company and it works quite well, but i dont know if i missed a table which might cause an integrity check error


I always think of the future and dont really like to add stored procedure to the database because after a while you dont remember why you have put them there.

what can you suggest, I'm leaning towards the StoredProc with its 3 Parameters.

Go No Go ?

cheers

 

[Beachless]

You can copy straight from excel into a DB table then use a SP to manipulate the data into the proper DB, you can also use a ETL(Extract Transform Load) tool to import from the spreadsheet into the DB.

Or various other custom things.

Try a few ETL tools though this is what they were made for.

 

[SBSP]

You can copy straight from excel into a DB table then use a SP to manipulate the data into the proper DB, you can also use a ETL(Extract Transform Load) tool to import from the spreadsheet into the DB.

Or various other custom things.

Try a few ETL tools though this is what they were made for.

will have a look at the ETL thing,

you mean copy and then paste the data from excel into the DB via the SQL management studio ?
I need to make a solution which is user friendly , i cant allow them access to our DB via the SQL Management Studio.

 

[Beachless]

will have a look at the ETL thing,

you mean copy and then paste the data from excel into the DB via the SQL management studio ?
I need to make a solution which is user friendly , i cant allow them access to our DB via the SQL Management Studio.

Yes, but that would be a once off to get the data off the spreadsheet and then move to the new system.

The etl tool can be used to do monthly,weekly,daily etc imports of the spreadsheet but ultimately they need to switch over to the front end(You may need to modify the existing one or design a new one ).

Without knowing the entire system its hard to give the right advice, I'm just not a fan of the VB script updating the DB as it could open you up to risks.
For instance what happens if 2 people are updating the spreadsheet at the same time or they update it offline?