Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update SQL database from excel

Posted on 2008-06-10
3
Medium Priority
?
1,331 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
  • 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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

916 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