How to load data into table1 from table 2 with different column data types?

Database
Highlighted
Enthusiast

How to load data into table1 from table 2 with different column data types?

Hi,

 

data was loaded into teradata table1(WRK table) from hadoop during which all column data types were converted to VARCHAR, how to load data from table1 to table2(original table) without running into error?

 

Do we have to use ETL process? please provide your valuable suggestions, thank you.

 

Interger was converted to Varchar

date was converted to Varchar 


Accepted Solutions
Teradata Employee

Re: How to load data into table1 from table 2 with different column data types?

Use the cast function. For example,
Insert into table2 Select ..., cast(colM as int), ..., cast(colN as date format 'yyyy-mm-dd'), ... from table1;

Dates can have many formats. I don't know what is in table1 of course; this is just the most common example.

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: How to load data into table1 from table 2 with different column data types?

Use the cast function. For example,
Insert into table2 Select ..., cast(colM as int), ..., cast(colN as date format 'yyyy-mm-dd'), ... from table1;

Dates can have many formats. I don't know what is in table1 of course; this is just the most common example.

Enthusiast

Re: How to load data into table1 from table 2 with different column data types?

Thank you for walking me through the correct path. I was able to insert with the suggestions provided.

 

How to i convert varchar to timestamp? i am usig this but getting an error:  INSERT Failed. 6760:  Invalid timestamp.  

 

SyntaxEditor Code Snippet

CAST(ART_CREATE_S AS TIMESTAMP(0) FORMAT 'YYYY/MM/DDBHHMISS')

 

 

Teradata Employee

Re: How to load data into table1 from table 2 with different column data types?

That is the correct approach but it looks like either that is not exactly the format of the varchar or the column has bad data.  You would have to look at the data and see where this format doesn't match.