Select Into from Linked server

  • 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

  • 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!

  • 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?

  • 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

  • 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!

  • 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