March 1, 2007 at 8:34 am
Hello forum,
I have a question regarding the transformation of data from one database to another. I thought I had this problem sorted but the "management" have moved the goalposts... again.
Anyway, here is the problem....
I have a bunch of records (a couple of million) that have columns that need to be transformed to another value based on there original value. The number of values to test for is 1200. A "case" statement in the query was my first though however, it breaks SQL Server (there is a KB article on it). What makes it more complicated is that I have another transformation in the same table for around the same amount of test values, however, the tests are made on the values from the first routine 🙁
I am fairly familiar with PERL and thought of using that from within an ActiveX script in DTS but I can't find much in the way of help on this.
I am looking at a number of other possible solutions (temporary tables, export the data to a text file and run PERL over that), however, if anyone else has come across this problem and found a neat(ish) solution I would be most grateful for advice.
many thanks
Nigel.
March 1, 2007 at 9:23 am
Sounds like the following process may work for you.
Import your data, as is, into a temp table.
Set up your conversion temp tables.
Create convtable1 (OriginalValue int, NewValue varchar(10))
Create convtable2 (Value varchar(10), Code varchar(10))
Create your Final Table.
Run a stored proc to insert into your new table using a Join on your Temp table(s).
INSERT INTO tbl_Final
SELECT tmp.col_1,
tmp.col_2,
conv1.NewValue,
conv2.Code,
tmp.col_4,
FROM #temptable tmp
JOIN convtable1 conv1 ON temp.col3 = conv1.OriginalValue
JOIN convtable2 conv2 ON conv1.NewVaue = conv2.Value
Regards,
Matt
March 1, 2007 at 9:28 am
Hello Matt,
Thanks for the prompt reply 🙂
One thing I forgot to mention (sorry) is that when this is done in a "live" situation, the source and destination databases are completely separate. Not sure if this effects your solution.
Nigel.
March 1, 2007 at 9:38 am
Shouldn't matter.
USE DTS to get your data from the original DB.
Set up your Final and conversion tables in the destination DB.
Once the DTS imports your data it's all local and your just using a stored proc to do the remaining data manipulation.
Actually, if you can set up the temp table structure, create and fill the conversion tables ahead of time you can then create the proc ahead of time and the run the proc as a step in DTS once the import is successful.
It takes some work, but once you get it set up you can then run the full import/convert process over and over til manamgement is happy with your changes.
Regards,
Matt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply