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

x
?
Solved

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
6
Medium Priority
?
239 Views
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.
0
Comment
Question by:Sp0cky
  • 3
  • 3
6 Comments
 
LVL 22

Expert Comment

by:PedroCGD
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.
Regards!
Pedro
0
 

Author Comment

by:Sp0cky
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"
0
 
LVL 22

Expert Comment

by:PedroCGD
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.
Regards
0
Industry Leaders: 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

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

Author Comment

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

Accepted Solution

by:
PedroCGD earned 2000 total points
ID: 22813161
You have several components in SSIS.
Basically you have SOURCES, TRANSFORMATIONS and DESTINATIONS.
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.
Regards!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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.

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