Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: Help Updating Tables From TAB File

    Hi All

    Im Really New To SQL Server and need to do the folowing - if anyone can help out i will be VERY happy

    i have a single table in sql server containing products im selling

    withing that table is 2 fields that i need to be able to update without changing any of the other fields in that line. (i need to be able to update price and stock field)

    there is a field that is unique (field with the barcode)

    the data i need to import is from a TAB text file which contains the barcode, stock and price field

    hope this makes sense and if anyone can help thats great

    cheers

    Ben

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    You can use DTS to import data which will suck data in from the tab file. However, unless you have a bit of experience with SQL its probably worth trying this on some dummy tables to get it right.
    You could use DTS to import the tab data into a new table. Then write some code to work your way through the new table to update the existing production table.

    If you wanted to update a column based on a unique column value in a table you could use:

    Update <tabel_name>
    set <column_to_be_updated> = '<some_value>'
    where <unique_column> = '<some_value>'

    i.e.

    update table_product_data
    set saleprice = '100'
    where barcode = '097364543'

    This ensures only the saleprice column in the table is changed where the barcode column = some unique value. That way you can selectively target a row in a particular column based on a value in the barcode column on the same row. Make sure that the barcode column ( or which ever column you use as the primary key for the table ) has unique values, otherwise you may get multiple rows in the saleprice column being updated at the same time.

    Post back if probs.

    Cheers,

    SG.

  3. #3
    Join Date
    Sep 2003
    Posts
    5
    thanks for the reply

    the problem is i am cool getting it to update 1 record its getting it to run thro a massive tab file and updating about 1500 prices

    any suggestions for a thick person ?

    cheers

    ben

  4. #4
    Join Date
    Sep 2003
    Posts
    5
    right i have realised how to do this now - if anyone can give me some example code for in sql to loop thro i have made it import into a new table and i under stand the code you put before but im not sure how to make it loop thro

    hope that makes sense!

    cheers

    ben

  5. #5
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi there!

    Here I can give you a little example code. Let's say that:
    1. "original" - is the table in the database
    2. "new" - is the table which has been filled with the data out of the TAB file

    update original
    set original.stock = new.stock,
    original.price = new.price
    from original
    ,new
    where original.bar_code_id = new.bar_code_id

    Hope that helps you further. If not, post a reply!

    Greetings,
    Carsten

    NOTE: This will not bring any new bar_code_id into "original"! It just updates existing id's

  6. #6
    Join Date
    Sep 2003
    Posts
    5
    thats great thanks

    will that loop thro each result in the table new ?

    thanks for the help

    Ben

  7. #7
    Join Date
    Sep 2003
    Posts
    5
    you both rule!

    its all working now - you cant believe how happy i am

    please both check you private messages

    thansk ben

Posting Permissions

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