SQL 2005 How to?  I would like to import an excel spreadsheet as a table, merge it with another table and export.

Posted on 2008-10-27
Last Modified: 2012-05-05
What I am trying to do:

1. Import an excel file full of users into SQL.
2. SQL compares that file to an existing database and adds new users only.  Some of the users will be duplicates but I do not want it to re-add old users.  I only want their names entered once of course.
3.  SQL changes the headers to match my required headers like lastname changes to 'last names' for example.
4. SQL exports table already in proper AD format (per number 3 above) to csv file.

I need as much of this to be automated after initial set up.  Can this be done?  How?  Thanks experts.
Question by:Sp0cky
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
  • 3
  • 3
LVL 22

Expert Comment

ID: 22811715
You should fo that using SSIS (SQL Server Integration Services). SSIS is the new version of old DTS
Attach here the examples and I can try you more, doing a example.

Author Comment

ID: 22811806
For starters, I thought I could execute the change of headers by doing this:

SELECT lastname AS surname, firstname, [phone number] as phone, email as mail
FROM yourtable

If you're creating a new table:
INSERT INTO newtable
SELECT lastname AS surname, firstname, [phone number] as phone, email as mail
FROM yourtable

But it is giving me an "invalid object " error next to "yourtable"
LVL 22

Expert Comment

ID: 22811826
You need also to add a lookup to your transformations... If you want to automatize, why you dont use SSIS? The components in SSIS are optimized to do this job.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 22812174
Sorry what is a lookup and transformation.?

Author Comment

ID: 22812565
Also, how does it know which databases to pull the tables from?
LVL 22

Accepted Solution

PedroCGD earned 500 total points
ID: 22813161
You have several components in SSIS.
You should take a look to some articles about SSIS. It's easy, intuitive, flexible and optimized.
Lookup is a component to make lookups in datasets... is a transformation type...
Check also WROX books...
I can try to search for a book here and send to your email.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

631 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