Copy of attribute data (or) Loss of data in a schema | Community
Skip to main content
Venu_Reddy
Venu_Reddy
Level 6
January 18, 2019
Solved

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

  • January 18, 2019
  • 4 replies
  • 2319 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Jean-Serge_Biro

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

4 replies

Jean-Serge_Biro
Jean-Serge_BiroAccepted solution
Level 10
January 18, 2019

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
    Community Advisor
    Jonathon_wodnickiCommunity Advisor
    Community Advisor
    January 18, 2019

    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
    Venu_ReddyAuthor
    Level 6
    January 21, 2019

    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
    Venu_ReddyAuthor
    Level 6
    January 21, 2019

    Hi Jean,

    Thank you for your help.

    Finally achieved the expectation and resolved

    Terms & ConditionsAccessibility statement

    Loading footer...