DTS question

[PhilPPhillips]

I am trying to copy data from a table in one database to a table in another database. The two table definitions are the same. The catch is that I need to modify some of a certain field's data to avoid duplicate data.

For example, if I try to combine the two customer master records I must change the customer number during the transfer because both databases use the same customer number range. The data wouldn't transfer anyways since this field is also a unique identifier.

I haven't been able to figure out how to do this using the DTS wizard and my software provider (Exact/Macola) says it can't be done via DTS.

I have many tables to copy over and was trying to avoid SQL scripting it all.

Thanks...

 

[nigelrivett]

Why use dts? You could just write a stored proc to do it. In the slect statement change the contents of the field.

insetr tbl
select col1, col2, col3 = 'whatever', col4, ...
from otherdb.dbo.tbl

If you want to use dts then the easiest way would be in the data transformation task make the source a query and in that select make the change as above.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.