SQL Query How do I mass update or insert?

I have an Excel sheet looks like this:
PartID MinQty MaxQty
Par1     10        20
Part2     15       20
Part3     20       30

I need to update this to database like:
PartID    Warehouse   MinQty      MaxQty
Part1         USA             0                0
Part1         USA             0                0
Part2  .............so on....................
Part2
Part3
I used below and worked, but I only can do this by one by one.
>>update tabl set minqty='10', maximumqty='20'
>>where partID='part1' and warehouse='USA'
Is there any way I can do this in batch?
I do not mind hand crafting Excel to txt etc.

creformAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Bill BachConnect With a Mentor PresidentCommented:
Easiest way is to use a full-fledged ETL (extract/Transform/Load) tool, which could read Excel directly and update your records with the needed data.  I have used Pervasive's Data Integrator to do this (and I know that it could do it directly after about 5 minutes work), but this is a paid solution.  Might also be able to do with open source solutions Pentaho or JasperSoft.

Otherwise, the next best option is to save the Excel as CSV, then write a parser that parses the text and changes  it to UPDATE statements, then run all the statements through to the target database.

Might also be able to write a SQL query to the Excel/ODBC data source and have IT craft the statements for you, but this would be a bit more complicated to set up.

If this is a one-time thing, I'd recommend the latter two options.  If you'll need to do this on an ongoing basis, I'd recommend an ETL tool for the job.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.