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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.