SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Apr 2004
    Location
    Brazil
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS SQL Bulk Update

    Hi,

    I've got a product table with about 16000 items. I now need to update about 300 of the quantities. The update information is in a simple Excel sheet consisting of 2 columns (product_name, qty).

    Rather than doing 300 plus individual updates, how best would I perform a bulk update?

    Thanks

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Easiest way--use Access. Import excel table, link to SQL table, do update query. At least if it is a one off type of thing.

    Other method would be to setup a DTS package which is a bit more involved.

    WWB

  3. #3
    SitePoint Guru silver trophy zakruvalcaba's Avatar
    Join Date
    May 2003
    Location
    San Diego
    Posts
    786
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Zak Ruvalcaba, MCAD, MCSD
    SitePoint Author: Build Your Own ASP.NET Website using C#/VB.NET
    Sams Author: Dreamweaver MX 2004 Unleashed

  4. #4
    SitePoint Member
    Join Date
    Apr 2004
    Location
    Brazil
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99
    Easiest way--use Access. Import excel table, link to SQL table, do update query. At least if it is a one off type of thing.

    Other method would be to setup a DTS package which is a bit more involved.

    WWB
    Hi,

    Thanks for the response... I guess my question then is:

    How do I build the Update query??

    I've got no problem using the DTS functionality, I just don't see how I go about getting the destination table to update only those rows which are contained in the source...

    Thanks

  5. #5
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Just import the excel spreadsheet into a temp table, and do an update off of an INNER JOIN on your products table.

    Make sure you do a standard select statement first to make sure your query is perfect before you update your products table. The last thing you want to do is mess up 16000 records.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •