Home » SQL ServerRSS

Set datatype of column within SELECT INTO query?

how do i define the datatype of a column within a SELECT INTO query?

so for example

SELECT
Id,
Date,
Age,
'' as subject
into mynewtable
from mybase

After this query is ran i need to manually update the datatype of this table to real from varchar(1)

can i update the dataype within the SELECT INTO query or can i do this outside

'

 

4 Answers Found

 

Answer 1

how do i define the datatype of a column within a SELECT INTO query?

so for example

SELECT
Id,
Date,
Age,
'' as subject
into mynewtable
from mybase

After this query is ran i need to manually update the datatype of this table to real from varchar(1)

can i update the dataype within the SELECT INTO query or can i do this outside

'


Sam, which column do you mean?  In general use the CAST() function to set the datatype from a select statement.

I don't see any of these that look like they have a datatype of real.  Also, casting a real to a varchar(1) looks like something that might be likely to cause problems -- it doesn't look like there is anything in your select statement that should be cast as varchar(1).

Case(age as varchar(1)) is likely to truncate as is everything  else; what exactly do you mean?

 

Answer 2

sorry i meant column subject

I use the following script to create it but i need to change it to a datatype of 'REAL'

can this be achieved

 

Answer 3

Try

SELECT 
Id,
Date,
Age,
CAST(0 asreal) as subject -- '' will be converted to 0 anywayinto mynewtable
from mybase



Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
 

Answer 4

Definitely give it a try as Naom has posted.  Also, try to avoid using '' to create numeric data types.  While this works in SQL Server, casting '' or ' ' as a numeric datatype will not work in Oracle nor in DB2.  Use 0 instead.

 

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter