Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2012
    Posts
    6

    Unanswered: update table from excel file (SQL 2000)

    Hi, ( I use sql 2000 )
    I have an excel table containing some fields with the first field named code,
    the same table with more fields exits in my sql database, the issue:
    I wan't to UPDATE all of the fields in sql-db that matches the ones in the excel file where code of the record matches, in sql it sorta look like this:

    update mytable (Fields 2, 3, 4,... till last one /the first is the code field/)
    select * from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=C:\Book1.xls','select * from [Sheet1$]')
    WHERE mytable.code = myexcel.code

    Appreciate your help.
    Salah
    Last edited by salaho1989; 10-20-12 at 04:33.

  2. #2
    Join Date
    Oct 2012
    Posts
    6
    really sorry if this issue have been posted before but I always find answers to be short of what I want.

  3. #3
    Join Date
    Oct 2012
    Posts
    6
    Am I going to get any answer at all guys?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, your idea will work with a few additions.

    First and foremost, can you use the DTS (Data Transformation Service) feature that is part of SQL 2000? This would be dramatically easier and also more robust than coding a file import solution in Transact-SQL.

    Second, does it make sense to import the worksheet in-toto, then manipulate it as a SQL table instead of dealing with the complexity of two similar but still different formats?

    As a last resort, you could definitely code what you've described so far using Transact-SQL. INSERT any code values from the spreadsheet into your SQL table. Then you can just UPDATE the sppropriate columns using the FROM clause within the T-SQL UPDATE statement.

    Just as a side note, you are looking for free advice from volunteers when you post on DBForums. Not everyone monitors the forums every day or every hour. You need to have a bit of patience!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2012
    Posts
    6
    Thnx on the reply and I'll keep in mind the Patience part
    as for DTS i'm aware of it's capabilities and such but my aim is to complete this using T-SQL only.
    As a last request (for which I can wait) let's say that my sql table is like this
    tblsql (GUID , Name , email)
    as for the excel
    tblxls 'Book1.xls/sheet$1' (GUID , Name , email)
    now I want to update my sqltbl from my tblxls using update statement(sqltbl will change it's Name&email values according to tblxls where GUID matches from both tables, It would mean a lot if u could write down the code for me, Please!!

  6. #6
    Join Date
    Jun 2011
    Location
    New Delhi India
    Posts
    9
    can you use the DTS (Data Transformation Service) feature that is part of SQL 2000? This would be dramatically easier and also more robust than coding a file import solution in Transact-SQL.

  7. #7
    Join Date
    Oct 2012
    Posts
    6
    yes I can use DTS I know that but the issue is that i'm trying to do this in code because it's a part of a software I'm developing and this transaction is going to be done more than one time with the same details so i need to code it so that the user won't have to go into sql to do so!
    And even if it's a long code to write i just need it done for 2 fields as I explained above and I will do the same for the whole table.
    so please I will appreciate any help you can provide me with.

Posting Permissions

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