Create Files from Records
Posted on 2011-05-12
We have a third party piece of software which will be getting an upgrade in the near future. One of the changes that will occur to the database is that all text fields will be changed to varchar(4000) datatype. Therefore, I need to deal with all records that currently contain more than 4000 characters or else they will be truncated during the upgrade.
I have run some preliminary queries and found that there are only two tables that will be affected. One only has four records too large so that is not an issue. The second table has 562 records greater than 4000 characters, which isn't too bad considering there are over 100K records in that table.
Without going in to too much detail about the software, it is a case-based system. You bring up a case and a number of features are available. Actions, people, notes, fees, documents, etc. The offending records are located down in the actions section, where you may find many different actions. The notes on a specific action will be where the large chunk of text lives. The document section allows you to attach documents of many different file types. When attaching a document, the software copies it to a central location and a record is added to a documents table containing the path, filename, etc.
My initial plan on how to tackle this problem goes like so :
1) Create a script that will go through the actions table and take all the records with 4000+ sized text fields and export each record's text field to an individual file with a specific name.
2) Replace that records text with a message similar to "[This note has moved. Please check document section for full notes.]"
3) Add a record to the document table that points to the newly created files, so the document section will then contain a new document containing the text from the old record.
I shouldn’t have a problem with 2 and 3, but my question to the experts is how to create a script to loop through all records with 4000+ records and export each one to an individual file.
This is just my initial thoughts on how to tackle this issue. If someone has another approach I would open to hearing it as well.
P.S. The database is on a SQL Server 2008 box if that matters, and we also have full access to it.