1

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?

2
  • 1
    I don't believe. Miracles do not happen. The amount or records inserted (while no hidden factors affected - constraints, triggers, etc.) is to be equal to the amount of records selected.
    – Akina
    May 18, 2018 at 9:41
  • Was the table empty before you copied (inserted) the data? Was the table non-existant before you copied the data? You might be better off perofrmaing a DROP TABLE temp_b before you run the SELECT ... part.
    – John K. N.
    May 18, 2018 at 12:49

1 Answer 1

4

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!

5
  • 2
    I'm feeling too lazy to write my own answer, so just going to add to yours, Brent! Even with the default isolation of READ COMMITTED, data movement during the select can cause a row to be read twice (or not at all). So, even without a (nolock) hint, that's possible. (and more likely on a large/busy table)
    – AMtwo
    May 18, 2018 at 14:38
  • Thanks man. That was kinda what I was hinting at with the "concurrency" answer, but I didn't quite wanna go to the level of detail of posting a demo. Kendra has a phenomenal index intersection demo for this that I love.
    – Brent Ozar
    May 18, 2018 at 16:12
  • Could you pls post a link to it?
    – dean
    May 19, 2018 at 13:46
  • @dean it's not publicly available, sorry - it's in our Mastering Query Tuning training classes.
    – Brent Ozar
    May 19, 2018 at 15:32
  • @BrentOzar is a scholar that still remember what it means to be a newbie. This platform needs more examples of such politeness May 21, 2018 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.