?
Solved

SQL Query How do I mass update or insert?

Posted on 2008-06-16
3
Medium Priority
?
3,164 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
1 Comment
 
LVL 29

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

571 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