asked on

Update SQL database from excel

Hello All,

I have a table in a SQL 2005 database that needs to be updated. There is one field which is currently blank and needs to be populated.
The Excel file which contains the needed data is broken down into 2 columns. Column1 and Column2.
When the data gets imported to the table I would like only records which match the data in column1 of the excel file and a field in the table which contains a code, 'TNB', to be updated with the data in column2 of the excel file.
Below is some sample data.
Column1 Column2
Vendor123 9987
Vendor124 9645
Vendor125 9912

The SQL table looks like the following
Vendor Code ScheduleID
Vendor123 TNB To be populated(Currently blank)
Vendor124 TNB To be populated(Currently blank)
Vendor125 TNB To be populated(Currently blank)
Vendor126 DAO Not to be populated(Currently blank)
Vendor 127 DAO Not to be populated(Currently blank)

Eventually I would like the SQL table to look as follows

Vendor Code ScheduleID
Vendor123 TNB 9987
Vendor124 TNB 9645
Vendor125 TNB 9912
Vendor126 DAO Blank
Vendor 127 DAO Blank

As you can see there are records in the table which will not get any update from the excel file.

What is the best way to update this table with the data from the excel file?

Any help is appreciated. Thanks.

caseyrharris

You can import the excel file in SQL Server 2005 Management Studio
by
1. right clicking on the database you would like to load it. select tasks -- import data
2. Follow the steps in the import wizard naming the table you would like to import into (When importing you are basically setting it up in a table on the db as a staging area you can name it whatever you want and after utlizing you can delete the table)
3. Now you have your excel data in a table in sql and you can now use an update script to update your original sql table.
-----
BEGIN TRAN
update sqltable
set scheduleid = excel_table.col2
where excel_table.c0l1 = vender and code = 'TNB'
SELECT * FROM SQLTABLE
ROLLBACK TRAN

if this looks the way you want it take out begin tran and rollback and execute.

ASKER CERTIFIED SOLUTION

caseyrharris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kwoznica

ASKER

Right on point. Thanks. I'll be posting another question regarding this topic soon, so if your looking for more points keep a look out.