Solved

Update SQL database from excel

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlServer Table Triggers 3 28
How to keep a record with the highest value 3 39
SQL Server Resume 5 45
Finding Where Clause Value in SQL Views and SP 21 38
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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

749 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