none
What does SELECT * INTO do in the background ?

    질문

  • Hi Guys,

    Today I came through the weird issue for which I need your help. Basically, I am creating the table(temp_b) from another table (temp_a) using below query

    select * into temp_b 
    from temp_a where cast(date_from as date)>='2010-01-01'
    and cast(date_from as date)<'2018-01-01'


    Temp_a table Structure and sample data:
    id int primary key,
    name  varchar not null,
    date_from datetime,
    update_time getdate()

    Temp_A 
    ID name  date_from  update_time 
    1 A 2010-01-01 2010-01-01
    2 B 2011-02-02 2011-02-02
    3 C 2012-02-02 2012-02-02
    4 D 2013-09-09 2013-09-09
    5 E 2014-08-06 2014-08-06


    Above query results duplicate records in <g class="gr_ gr_1094 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="1094" id="1094">temp_b</g> table.

    Temp_B
    ID name  date_from  update_time 
    1 A 2010-01-01 2010-01-01
    1 A 2010-01-01 2010-01-01
    2 B 2011-02-02 2011-02-02
    3 C 2012-02-02 2012-02-02
    3 C 2012-02-02 2012-02-02
    4 D 2013-09-09 2013-09-09
    5 E 2014-08-06 2014-08-06

    Can someone please elaborate what would be the reason for duplicate records in destination table when there is no duplicate in the source table.

    Or How "Select * into" works in the background

     



    malik

    2018년 5월 18일 금요일 오전 5:40

모든 응답

  • Hi,

    Are you sure that you didn't run the select into query twice? because there is no reason to have duplicate records when there is no duplicate in the source table.

    Try to truncate table_b and run the query again, see if the issue still going.


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    2018년 5월 18일 금요일 오전 7:44
  • Sorry that doesnt make much sense

    I think that your actual query is different and you're using some join logic in your select into which is causing the duplicates.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오전 8:37
  • I have to drop the table before run query the second time.

    malik

    2018년 5월 18일 금요일 오전 9:29
  • Not using any joins condition in the query

    malik

    2018년 5월 18일 금요일 오전 9:30
  • Not using any joins condition in the query

    malik

    Are you sure temp_a is a table and not a view?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오전 10:07
  • Can someone please elaborate what would be the reason for duplicate records in destination table when there is no duplicate in the source table.

    Or How "Select * into" works in the background 

    I can't say for sure why you get duplicates without a full script that demonstrates the problem. I'm guessing the one you posted is a simplified example and some important details were not included.

    One possibility is the actual query was run from a READ_UNCOMMITTED session or with a NOLOCK hint. In that case, rows can be skipped or duplicated as they are read from the source table due to an allocation order scan while the source table is updated from other sessions.

    Regarding SELECT INTO behavior, the statement internally creates a new table matching the source schema and auto-commits that transaction. The new table is then loaded from the source query and auto-committed upon success. This is functionally identical to a separate CREATE TABLE followed by INSERT...SELECT.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • 답변으로 제안됨 Sarat (SS) 2018년 6월 15일 금요일 오후 8:58
    2018년 5월 18일 금요일 오전 10:42