Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query How do I mass update or insert?

Posted on 2008-06-16
3
Medium Priority
?
3,162 Views
Last Modified: 2012-08-14
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.

0
Comment
Question by:creform
3 Comments
 
LVL 28

Accepted Solution

by:
Bill Bach earned 2000 total points
ID: 21797490
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question