I was copying data using a select insert statement, it was a quick and dirty way to copy the missing data which was accidentally deleted a week before.
Since I had no admin privilides on the production, the script was executed by the DBA which resulted in the duplication of data, the DBA did execute the script while on screenshare where I made sure they did not execute the script twice.
The script ran successfully and all the data was copied in the target tables, but the data was only duplicated in 2 tables out of 23 tables.
Below are the details of the sever:
- Database – Microsoft SQL Server 2019
- Version – 15.0.4382.1(x64)
- Server – Windows Server 2022 Standard 10.0 (x64)(Hveprvisor)
- CPU – 4
- Memory – 64GB
I used the below script to copy the data
INSER INTO target_db.dw.table1
SELECT *
FROM backup.dw.table1
WHERE column1 IN (
'value_1',
'value_2',
'value_n'
)
The script was written for 23 different tables and it ran successfull for all tables except table1 and table2. The logs that were generated while running the script first time resulted in following data count for the two tables:
(86997 rows affected).
(371154 rows affected)
The next day when I checked the data by using a count(*) statement it was effectively doubled and upon checking thoroughly the lines that were inserted were duplicated. All other data in the target table remained intact and no duplication occured.
This activity was performed twice with a gap of 4 days, different set of filters were used for the insertion on both days depending upon the missing data ending in same result where the lines which were inserted from the backup were the only ones which got duplicated. The backup contains no duplicates, the script was not run twice, the duplication only affected the data in two tables and specifically the data which was copied over from backup to target.
Could anyone please help what went wrong?
1