Post reply

How do you Update a Field in an SQL table using Data from Excel

  • August 5, 2008 at 9:47 am

    #190453

    Hi,

    Can you help pls.

    I get a list periodically in an Excel format of people who have changed addresses since the last period. The spreadsheet usually has 4 fields (MemberID, Add1, Add2, Add3).

    I would normally go into the our sql database and use an update statement to MANUALLY change those corresponding records/fields in the table.

    This list is now getting longer and I am wondering if there's a way to AUTOMATE this - i.e. write an Update query that looks at the MemberIDs in the Spreadsheet and updates the relevant records in my table with the new corresponding addresses in the spreadsheet.

    Thanks for your help.

  • August 5, 2008 at 10:10 am

    #853302

    One way to achieve it could be to import your excel file into a table and the update the fields in the destination table.

    If you want something more complex, try taking a look at SSIS (SQL Server Integration Services).

    Regards

    Gianluca

    -- Gianluca Sartori

  • August 5, 2008 at 11:37 am

    #853377

    I'd go with something like Gianluca's idea. SSIS or import to a table.

  • August 6, 2008 at 8:10 am

    #853837

    I would agree that creating an SSIS package to import the excel and using that imported table as the source is probably the best option. A less secure option is the OPENROWSET() commands to open it on the fly and query directly from the file.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply