Solved

Update SQL database from excel

Posted on 2008-06-10
3
1,250 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
semaphore timeout period has expired 1 33
What is needed to become a DBA? 7 56
Restore a log backup compressed 3 17
learning MS SSIS 13 26
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

710 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