abs786123 abs786123 - 1 month ago 7
SQL Question

T-SQL cast and converting issues from source tables to destination

I have a table as follows:

create table dbo.##Table_A00
(
RowNo int,
TRANSACTION_TYPE varchar(3),
ORGANISATION_ID numeric (10),
FILE_TYPE varchar(3),
CREATION_DATE datetime,
CREATION_TIME varchar(3),
GENERATION_NUMBER numeric (6)
)


However the source files I am using is a table used to capture flat files and they can be in any data format.

What I have in the source table the data type looks like this:

CREATE TABLE ##Table_Alltextfiles
(
rowID int identity (1,1),
[Col1] varchar(50),
[Col2] varchar(250),
[Col3] varchar(50),
[Col4] varchar(50),
[Col5] varchar(50),
[Col6] varchar(50),
[Col7] varchar(50)
)


What I want to do is insert into
##Table_A00
(destination) all rows from
##Table_Alltextfiles
(source)

However I am having issues doing this as the data type are mis match and I have tried casting it without success.

What can I do to get the value of varchar to its appropiate destination i.e if its a date field or if its a numeric.

This is what I have been tring to do:

insert into dbo.##Table_A00
select
rowID,
col1, cast(col2 as numeric (10)),
col3, cast(col4 as date),
col5, cast(col6 as numeric (6))
from
##Table_Alltextfiles
where
col1 = 'A00'


Thank you

Answer

Try out with the below query.

insert into dbo.##Table_A00
    select 
        rowID, 
        SUBSTRING(col1,1,3), case when ISNUMERIC(col2)=1 THEN cast(col2 as numeric (10)) ELSE NULL END,
        SUBSTRING(col3,1,3), case when ISDATE(col4)=1 THEN  cast(col4 as datetime) ELSE NULL END, 
        SUBSTRING(col5,1,3), case when ISNUMERIC(col6)=1 THEN cast(col6 as numeric (6)) ELSE NULL END
    from 
        ##Table_Alltextfiles
    where 
        col1 = 'A00'
Comments