Solved

Update SQL database from excel

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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now