Solved

Update SQL database from excel

Posted on 2008-06-10
3
1,254 Views
Last Modified: 2010-04-21
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.                    
 
0
Comment
Question by:kwoznica
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 3

Expert Comment

by:caseyrharris
ID: 21752963
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.
0
 
LVL 3

Accepted Solution

by:
caseyrharris earned 500 total points
ID: 21752984
sorry it looks a little bit more like this....
BEGIN TRAN
update sqltable
set sqltable.scheduleid = excel_table.col2
from excel_table
where excel_table.c0l1 = sqltable.vendor
and sqltable.code = 'TNB'
SELECT * FROM SQLTABLE
ROLLBACK TRAN
0
 

Author Closing Comment

by:kwoznica
ID: 31465782
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.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question