Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2012
    Posts
    3

    Updating SQL table using an excel file

    New to SQL.
    I am trying to update a SQL table using an excel file which has 2 columns FMStyle and FMHSNum.
    FMStyle is my link to the SQL table.
    Here is what I have for code....
    --------------------------------------------------
    Update DataTEST.dbo.zzxstylr
    SET hs_num = (select FMHSNum FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\temp\StyleHSCodesLoad.xls;HDR=YES ', [Sheet1$]))
    Where FMStyle = zzxstylr.style
    --------------------------------------------------
    Everything seems to be ok except for the "Where FMStyle" is giving me a message Invalid Column name on FMStyle.
    Do I need to qualify FMStyle and if so how.

    Thanks
    Michael

  2. #2
    Join Date
    Jan 2013
    Location
    Woodland Hills, CA
    Posts
    18
    MichaelL, try this:

    Update zz
    set
    zz.hs_num = ex.FMHSNum
    from DataTEST.dbo.zzxstylr zz
    join OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\temp\StyleHSCodesLoad.xls;HDR=YES ', [Sheet1$]) ex
    on ex.FMStyle = zz.style

    YesAgile

Posting Permissions

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