MS SQL 2000 importing from csv file to update records

Hi SQL Gurus,

I'm sql neewb here and I'm trying to achive the following:

I have a table for user records and there's a specific column for email addresses that is not 100% updated yet...I created a query that displays: "FirstName", "LastName", and "Email" column data only.

I want to then save the results to a CSV file, distribute to a person to use a data entry form as he/she updates all the email addresses and then import the file back to the database and update where necessary.

What's the best way to achive this?  I repeat, I need step by step instructions since I'm neewb...thanks!
LVL 1
jetli87Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kenwagersCommented:
Here's my favorite method to do this type of update:

Run this query:

SELECT FirstName, LastName, Email
FROM TableName
WHERE IsNull(EMAIL,'') = ''

This will provide a list of all names where no email is present.

Copy the results the query window to an Excel worksheet.  Send that worksheet to your person to do the data entry.

When you get it back, add this formula to all rows in the column next to the Email field:
=CONCATENATE("update tablename set email = '",C10,"' where firstname = '",A10,"' and lastname = '",B10,"'")  

Watch the quotes - they are important.

Then, copy the resulting calculated values back to a SQL Server Query window and execute it.  Here's what they should look like:

update tablename set email = 'bob@test.com' where firstname = 'joe' and lastname = 'smith'
update tablename set email = 'mary@test.com' where firstname = 'mary' and lastname = 'jones'

Execute one line first (highlight and then F5) to make sure there are no typos.

If you have a Primary Key to the table, it would be preferable to include that in the output file, and then do the update based on that key instead of the name fields.  That will allow duplicate names with different emails (i.e. John Smith).


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jetli87Author Commented:
thanks for the response.

Here's what i got with the my query below...The XLS is attached.

I'm a bit confused on applying the formula...Can you added and send back?

Much appreciated.
select hmyperson as Primarykey, Sfirstname as Firstname, slastname as Lastname, saddr1 as Address, sunitcode as Unit#, semail as 'Email Address' from tenant where hproperty =4 and istatus =0
order by sunitcode

Open in new window

Sample.xls
0
kenwagersCommented:
Glad to.  That's often the hard part.
sample.xls
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jetli87Author Commented:
lol...omg, that makes so much sense...thanks!
0
kenwagersCommented:
I saw your table / field names after I posted it - this one has the right names and should be close to what you need.
sample.xls
0
jetli87Author Commented:
no worries, after looking at the first one, i understood the concept...thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.