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?

  • 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 '18 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 aka hot2use May 18 '18 at 12:49
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!

  • 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 '18 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 '18 at 16:12
  • Could you pls post a link to it? – dean May 19 '18 at 13:46
  • @dean it's not publicly available, sorry - it's in our Mastering Query Tuning training classes. – Brent Ozar May 19 '18 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 – Francesco Mantovani May 21 '18 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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