how does datatypes get set when using select into in sql server?

Question

how does datatypes get set when using select into in sql server?

dndaughtery

SSChampion

Points: 11290
More actions
December 30, 2015 at 6:47 am

#314625

I used a select...into to create a staging table. Its been fine but lastnight I got a truncation error. I was under the "Assumption" that sql server looked at the source table and retrieved the datatype from the column there.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

dndaughtery (12/30/2015)
I used a select...into to create a staging table. Its been fine but lastnight I got a truncation error. I was under the "Assumption" that sql server looked at the source table and retrieved the datatype from the column there.

It does, generally. Are you doing some processing on the source column, or is it a naked SELECT?

You can always force things by doing a suitable CAST.

Please provide some sample code which highlights the behaviour.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

dndaughtery SSChampion Points: 11290 More actions · Answer 2

There aren't any mathematical calculations but there are several Case statements. I'd post the query but its a monster

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

dndaughtery (12/30/2015)
There aren't any mathematical calculations but there are several Case statements. I'd post the query but its a monster

If a CASE statement is returning an item from a list of items with different types, the data type of the returned item is dictated by data type precedence.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

dndaughtery (12/30/2015)
There aren't any mathematical calculations but there are several Case statements. I'd post the query but its a monster

CASE [font="Arial Black"]IS [/font]a type of calculation. You may have to CAST the CASE to the maximum expected length of any result.

--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)

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

dndaughtery (12/30/2015)
I used a select...into to create a staging table. Its been fine but lastnight I got a truncation error. I was under the "Assumption" that sql server looked at the source table and retrieved the datatype from the column there.

For your CASE statement, SQL does not know what the resulting datatype should be.

As Phil suggested, use the CAST statement for any column in your results where you have done anything other than a pure SELECT. Use CAST to specify the desired data type for your CASE results.

Edit: oops, I see other were faster at posting their response.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]