insert into temp table

  • Comments posted to this topic are about the item insert into temp table

  • Nice question! Thanks.

  • Thanks,

    got it wrong and learning something 🙂

  • good question... thanks 🙂

  • got it wrong and i thought that, 1 as something in select into statement by default it will allows.but learned that it will not allows null.I worked more on select ..into but still got wrong.

    good question.learned one point.

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • Good Question.. i learn something new...:-)

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • declare @myothertab table (

    col2 varchar(20),

    col3 varchar(20)

    )

    insert into @myothertab

    select 'A','B' union all

    SELECT 'W','Q'

    ;

    declare @myanothertab table (

    col2 varchar(20),

    col3 varchar(20)

    )

    insert into @myothertab

    select 'A','B' union all

    SELECT 'W','Q';

    select 1 as col1, col2, col3 into #mytmp

    from @myothertab

    insert into #mytmp

    select null,col2,col3

    from @myanothertab

    select * from #mytmp

    returns 4 rows on SS2008 contrary to your explanation. why?

  • Hi,

    the point is the creation of a table column with a constant. - eg 1 or 'A' -

    in this case a not null constraint is added to this column when select .. into is used.

    on msdn nor the books online i've found why this happened.

    learned that by getting errors 😛

    kind regards Matthias

  • ma-516002 (6/17/2010)


    declare @myothertab table (

    col2 varchar(20),

    col3 varchar(20)

    )

    insert into @myothertab

    select 'A','B' union all

    SELECT 'W','Q'

    ;

    declare @myanothertab table (

    col2 varchar(20),

    col3 varchar(20)

    )

    insert into @myothertab

    select 'A','B' union all

    SELECT 'W','Q';

    select 1 as col1, col2, col3 into #mytmp

    from @myothertab

    insert into #mytmp

    select null,col2,col3

    from @myanothertab

    select * from #mytmp

    returns 4 rows on SS2008 contrary to your explanation. why?

    There's an error in your sql above. You're inserting twice into the same table (@myothertab) and not at all into @myanothertab.

  • i tried with "ma-516002" query in the 2005 version the results are same i am able to get the records from both the tables. when i tried something simila with my local database tables i am getting records from only one table.

    in both the cases it is creating table with the not null constraint

    looks Weird.....

  • sharath.chalamgari (6/18/2010)


    i tried with "ma-516002" query in the 2005 version the results are same i am able to get the records from both the tables. when i tried something simila with my local database tables i am getting records from only one table.

    in both the cases it is creating table with the not null constraint

    looks Weird.....

    use the corrected code below, and see what you get.

    if object_id('tempdb.dbo.#mytmp') is not null

    drop table #mytmp

    go

    declare @myothertab table (

    col2 varchar(20),

    col3 varchar(20)

    )

    insert into @myothertab

    select 'A','B' union all

    SELECT 'W','Q'

    ;

    declare @myanothertab table (

    col2 varchar(20),

    col3 varchar(20)

    )

    insert into @myanothertab

    select 'A2','B2' union all

    SELECT 'W2','Q2';

    select 1 as col1, col2, col3 into #mytmp

    from @myothertab

    insert into #mytmp

    select null,col2,col3

    from @myanothertab

    select * from #mytmp

  • Old Hand was a attentive reader and found the bug :w00t: in Ten Centuries code as follows.

    i must say a don't read the cod ereally because i got this constraint violation error many times in the past 😛

    declare @myothertab table (

    col2 varchar(20),

    col3 varchar(20)

    )

    insert into @myothertab

    select 'A','B' union all

    SELECT 'W','Q'

    ;

    declare @myanothertab table (

    col2 varchar(20),

    col3 varchar(20)

    )

    insert into @myanothertab /* < should be*/

    select 'A','B' union all

    SELECT 'W','Q';

    select 1 as col1, col2, col3 into #mytmp

    from @myothertab

    insert into #mytmp

    select null,col2,col3

    from @myanothertab

    select * from #mytmp

  • Nice question!

    I believe that far too many people use select into without really understanding what it does. This is not documented very explicitly; in ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/cc9bdf10-edfc-41a5-adf1-aa9715ed8d71.htm (link for SQL 2005 BOL), it says:

    "The structure of the new table is defined by the attributes of the expressions in the select list."

    This means that:

    1. Datatypes of the columns will be inferred from the expressions in the SELECT list. If it's just a column reference, the new table wil get the same data type. For an expression, the data type of the result expression will be used. And that includes constants. The constant 1 will be considered int; 'a' will be varchar(1) (yes, you read it right - varchar, not char), and 2.5 will be numeric(2,1).

    2. Nullability of the columns will also be inferred from the expressions. If the expression can (theoretically) return null, the column will be nullable, otherwise it won't. Constant expressions can never return null, nor can a simple reference to a column withh a NOT NULL constraint. Due to some weird implementation details, COALESCE is always nullable; ISNULL is nullable if and only if the second argument is nullable. That makes SELECT INTO one of the two cases where I use ISNULL instead of the more standard and more versatile COALESCE if I want to make a column NOT NULL.

    3. No other constraints are ever generated. No PRIMARY KEY, no FOREIGN KEY, no UNIQUE, no DEFAULT - nothing. Because of that limitation, I really recommend anyone to use SELECT INTO only for temporary tables, and even in that case using CREATE TABLE followed by INSERT SELECT is often the better option.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • I got it wrong as well, even though I "cheated" and tried it out before answering 🙂 I didn't have rows in myanothertable and then of course the not null constraint didn't trigger.

    Thanks for enlightning me.

  • Oops 🙂 Your right... it's way too early in the morning...

Viewing 15 posts - 1 through 15 (of 52 total)

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