Sp0cky
asked on
SQL 2005 How to? I would like to import an excel spreadsheet as a table, merge it with another table and export.
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.
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.
ASKER
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"
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"
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
Regards
ASKER
Sorry what is a lookup and transformation.?
ASKER
Also, how does it know which databases to pull the tables from?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Attach here the examples and I can try you more, doing a example.
Regards!
Pedro