How 'Select * into' works into background?

Question

Today I came through the weird issue for which I need your help. Basically, I am copying a table(temp_a) into table (temp_b) 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

But above query results duplicate records in temp_b 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?

Answer 1

You didn't include the exact query to reproduce the issue (it doesn't happen every time), so here's a few different ways it'll happen:

Using WITH (NOLOCK) - this hint in your query can cause records to be read twice, to be skipped altogether, and for your query to fail outright. (The same thing can happen with the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED hint.)

An error in the SELECT query - for example, if you're joining multiple tables together to get the data for the INSERT, and you have an error in the join (or a legitimate join that produces multiple rows of results), you can get duplicates.

Concurrency - say that while you're working, someone else is inserting and removing records from the source table. They might have temporarily had duplicates in the source table, and when you go to look later, they're gone. (I've hit issues where people would insert a new version of a row, then delete the old version of a row, so some selects out of that table would see duplicate rows. They couldn't implement unique constraints in the database because of that code pattern.)

To get an exact answer for your exact situation, post your exact code, or code that reproduces the issue you're having, and we'd be glad to dig deeper. And welcome to Stack!