import data but changing data along the way!!

[stewglyn]

I'm new to SQL Server and would like some help. I have a table in 2000 called clients. I want to import data in from another 2000 table called clients (which is in the original DB.)However the table in database 1 which I'm importing from has differing datatypes and sizes. How can I get the data in WITHOUT changing the new db2 to match the table schema of db1? There is vb code to do it if so what does it look like. Or is the dts a better bet.

Thaks alot in advance for any help

stewart

 

[BigMickeyD]

Data Transformation Services (DTS) is designed for doing just that. You would use two data sources and a VBScript or JavaScript transformation.

 

[SQLSister]

Actually, using an ActiveX script task can slow a transformation down . You should see if you can do it with T-SQL code using an Execute SQL task or using the data pump task.

Whatever you do, don't forget to figure out how to handle the records where the data conversion is not compatible. For instance where they used a text field in the data to be converted for the information to be passed to a date field in your table. The transformation works for most but not when they entered 'ASAP'. If you don't plan to check for and handle these things, your transform will bomb.