March 19, 2009 at 3:39 pm
We have sql server 2005 database and I need to fill out the description of different courses in a table.
Table is already populated except that field. The data type of this field is text.
I know it is going to be deprecated but I can't change because we bought the software from a vendor.
What would be the best method to achieve this?
March 19, 2009 at 5:55 pm
Do you have the descriptions already completed in soft format? A CSV file containing CourseID, Description would be an ideal starting point ...
If not, are you just looking for a way of accessing the table and typing in the descriptions?
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 19, 2009 at 9:32 pm
yes, I do have csv file with description and id. How do I proceed?
March 19, 2009 at 10:07 pm
Excellent. There are several ways - but the one I would choose is as follows:
1) It should go without saying, but use a copy of your prod database ...
2) Create a new table in your SQL Server database to hold the contents of your CSV file - tmpCourse, perhaps - containing only ID and Description. Make sure that the datatypes, lengths etc of the ID and Description fields match those of the destination table.
3) In SQL Server Management Studio, right-click the target database and select Tasks/Import Data. Follow the prompts to import your CSV file into tmpCourse - if you need any more detailed help with this part, just post again.
4) Once the data has been imported, issue some SQL to update your main table - along the following lines
UPDATE d
SET CourseDesc = s.Description
FROM Course d
JOIN tmpCourse s on d.ID = s.ID
WHERE (d.CourseDesc <> s.Description) or (d.CourseDesc is Null)
(untested)
5) Verify that all is well.
6) Delete tmpCourse and repeat in production - job done.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 20, 2009 at 8:45 am
thanks. I was thinking I have to do something special for text datatype.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply