February 21, 2017 at 6:34 am
Hi
we have a developer is using agent job running select into statements over linked servers to populate tables on a MI server
I noticed that the tables on the reporting server were larger than on the original, and after a little investigation noticed that all the columns were varchar instead of int, tinyint, datetime etc.
Looking at the documentation for Select Into it says that the data type is preserved. Is this correct? If it is normally what are the possible reasons for it not being the case here.
My other question is what's the impact of importing a date\time column into a varchar. I thought at best it will mean an implicit convert when the column is queried, at worst incorrect results being returned.
Cheers
Alex
February 21, 2017 at 6:56 am
it's true that SELECT INTO will have the datatypes preserved, but it depends on what the source is doing.
CONVERT functions ,ISNULL, or implicit conversions in a query(due to unions?) can change the datatype from what you wanted to something unexpected. also, it could depend on the ODBC driver you are using, if we are connecting to anything other than a SQL server.
If you get a chance to show us a script with an actual example of datatypes not persisting, I'd think we could help track down the issue.
Lowell
February 21, 2017 at 7:03 am
Thanks for the reply.
The linked server is to another SQL box and the script couldn't be simpler.....
SELECT
RTRIM(LTRIM(CONT_REF)) AS CONT_REF,
RTRIM(LTRIM(INITIAL_NUM)) AS INITIAL_NUM,
RTRIM(LTRIM(SECOND_NUM)) AS SECOND_NUM,
RTRIM(LTRIM(CON_REF)) AS CON_REF,
RTRIM(LTRIM(CONTA_REF)) AS CONTA_REF,
RTRIM(LTRIM(LASTUP_USER)) AS LASTUP_USER,
RTRIM(LTRIM(LASTUP_PROG)) AS LASTUP_PROG,
RTRIM(LTRIM(LASTU_INFTIM)) AS LASTU_INFTIM,
RTRIM(LTRIM(STATUS)) AS STATUS,
RTRIM(LTRIM(STATUS_AS)) AS STATUS_AS,
RTRIM(LTRIM(PROD_REF)) AS PROD_REF,
RTRIM(LTRIM(PROD_DESC)) AS PROD_DESC
INTO TABLE1
FROM [linkedserver].DAtabase1.DBO.CONTACT WITH (NOLOCK)
If the import table was defined before the select into was run would it retain the data types?
February 21, 2017 at 7:25 am
I often wish that SELECT INTO didn't exist, because I find it's often abused. You've nailed it, though - create the table once and then it's always there for you, with the data types, constraints and indexes you expect. And then whatever user executes this only needs DML permissions (no DDL permissions) in the database.
John
February 21, 2017 at 7:30 am
LTRIM + RTRIM is your problem.
all those LTRIM + RTRIM force the results to be varchars, regardless of what the original data types are. .
if the values were int or datetime, the LTRIM RTRIM force implicit conversions.
do you need to LTRTIM RTRIM an int? a datetime? no of course not.
doing it like this will preserve data types as expected: if some of those are actually varchar data types, only those columns should be trimmed....but you only do that if you KNOW ther eis a problem with preceding or trailing spaces.
SELECT
CONT_REF AS CONT_REF,
INITIAL_NUM AS INITIAL_NUM,
SECOND_NUM AS SECOND_NUM,
CON_REF AS CON_REF,
CONTA_REF AS CONTA_REF,
LASTUP_USER AS LASTUP_USER,
LASTUP_PROG AS LASTUP_PROG,
LASTU_INFTIM AS LASTU_INFTIM,
STATUS AS STATUS,
STATUS_AS AS STATUS_AS,
PROD_REF AS PROD_REF,
PROD_DESC AS PROD_DESC
INTO TABLE1
FROM [linkedserver].DAtabase1.DBO.CONTACT WITH (NOLOCK)
Lowell
February 21, 2017 at 7:40 am
Thanks Guys, that's really helpful..
I never like seeing Rtrim and Ltrim. Some Dev's seem to put them into any code by default.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply