Copy of attribute data (or) Loss of data in a schema

[Venu_Reddy]

Hi All,

Can we copy the column data in a schema into another?

I'm trying to change the data type of an attribute in a schema and worried that i lost the previous data which are already present in the attribute when i change the datatype.

For eg: Column/Attribute  A of type INTEGER has some data. Now i'm trying to change the data type to VARCHAR/STRING where i will loose the previous data.

Is there any possibility to avoid the loss of data?

Your response will be appreciate

Regards,

Venu

[Jean-Serge_Biro · Accepted solution]

Hi Venur,

This question is more related to the underlying database engine (RDBMS) than Adobe Campaign itself.So take care and check carefull,

For most database engines (SQL Server, Oracle etc), alter table modify column instruction would loose data if the datatype is not in the same family, and a supersed.

For instance, short int to long int, ok but long int to short you could loose values by the casting. The same from varchar(100) to varchar(200), no problem.

And from very different types such as int, date, string, you would loose all data.

So take care and check carefully depending on source/destination types, if a simple alter is possible or not.

So if not the case, the directive is:

1. create a new column.
2. copy (insert casting-converting instruction(col2) select  col1 SQL query) the current values of col1 casted/converted (using Convert, decode, and all the sql functions depending on your engine) into col2 column
3. suppress the col1 column.

Regards

J-Serge

[Jonathon_wodnicki]

Hi,

I do this all the time when fixing bad implementations. Just alter the columns as desired, then write your own SQL to effect the changes. Syntax varies based on the RDBMS you're using, for SQL Server 'alter table x alter column y varchar(30)' etc. This can be a slight challenge due to the autogenerated constraints Campaign adds to numeric types, though these are discoverable via workflow audit logs and can be dropped/recreated as part of the migration. Do not use the update data structure wizard here- it has no mechanism to detect in-place changes and will instead see the missing and new as drop and create.

Thanks,

-Jon

[Venu_Reddy]

Hi J-Serge,

Thank you for quick reply.

I created a new column and tried to copy the data from one column to another using decode (Source expression) but getting failed where i have to query it.

Can you please elaborate the step-2 with an example as it will be really helpful.

So if not the case, the directive is:

1. create a new column.

2. copy (insert casting-converting instruction(col2) select  col1 SQL query) the current values of col1 casted/converted (using Convert, decode, and all the sql functions depending on your engine) into col2 column

3. suppress the col1 column.

Regards,

Venu

[Venu_Reddy]

Hi Jean,

Thank you for your help.

Finally achieved the expectation and resolved