Select Into from Linked server

Question

Select Into from Linked server

alex.palmer

SSCrazy

Points: 2292
More actions
February 21, 2017 at 6:34 am

#340962

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

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

alex.palmer SSCrazy Points: 2292 More actions · Answer 2

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?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

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

Lowell SSC Guru Points: 323504 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

alex.palmer SSCrazy Points: 2292 More actions · Answer 5

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.