If I convert the data types like below will this the data types stick in the selected into temp

Question

If I convert the data types like below will this the data types stick in the selected into temp

adam spencer

Hall of Fame

Points: 3926
More actions
February 23, 2011 at 8:35 am

#232641

Hi,
If I convert the data types like below will this the data types stick in the selected into temp #5407?
select convert(varchar(50),HIC) as HIC,convert(varchar(10),ICD9)as ICD9,HCC, DOS
into #5407
from #134
thanks in advance!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Kiara SSCarpal Tunnel Points: 4185 More actions · Answer 1

adam spencer (2/23/2011)
Hi,
If I convert the data types like below will this the data types stick in the selected into temp #5407?
select convert(varchar(50),HIC) as HIC,convert(varchar(10),ICD9)as ICD9,HCC, DOS
into #5407
from #134
thanks in advance!

If you are asking if your temporary table #5407 will have a varchar(50) column named HIC and a varchar(10) column named ICD9, then yes, the data types will "stick". Basically, the data type of the column when you create a table with select into is based on what SQL thinks the datatype of the data is. You told it explicitly what the data types of the first two columns are by using an explicit conversion on that data.

You can test this here:

create table #134

(HIC int

,ICD9 smalldatetime

,HCC char(1)

,DOS char(1)

)

insert into #134

(HIC, ICD9, HCC, DOS)

values (3, '2010-11-3', 'B','D')

select convert(varchar(50),HIC) as HIC,convert(varchar(10),ICD9)as ICD9,HCC, DOS

into #5407

from #134

select * from #5407

You'll see that ICD9 has been converted to "Nov 3 201". Part of the result was truncated because the result was longer than the char field it was being stored in.

-Ki

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

I'll also add that if you use ISNULL as the final "step" in any of the column derivations, it will create a NOT NULL column in the Temp Table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)