Solved

Insert statement is inserting duplicate records

Posted on 2016-09-09
15
64 Views
Last Modified: 2016-09-13
Hi Experts,

In my project, I have a finger print scanner. When the students scan their finger, I insert the record in SQL Server. It is inserting duplicate records exactly at same time.


This is my query in the stored procedure.

if not exists(
                   select StdLogID,StdTimeIn from LOGINDETAILS  where userID = @UserID
                   and TodaysDate =FORMAT(GetDate(),'MMddyyyy')
                   and DATEDIFF(SECOND, StdTimeIn,GetDate()) < 60
             and StdTimeIn IS NOT NULL and StdTimeout IS NULL)
         begin
                   INSERT INTO dbo.LOGINDETAILS
                           (UserID,              
                              StdTimeIn,
                            TodaysDate    
                            )
                   VALUES
                              (@UserID,                                    
                              CURRENT_TIMESTAMP,
                              FORMAT(GetDate(),'MMddyyyy')                  
                            )
            end

1139      325      2016-09-09 06:52:28.057      NULL      09092016
1140      325      2016-09-09 06:52:28.057      NULL      09092016
1141      325      2016-09-09 06:52:28.057      NULL      09092016
1154      329      2016-09-09 06:53:38.033      NULL      09092016
1155      329      2016-09-09 06:53:38.033      NULL      09092016
1167      481      2016-09-09 06:55:34.737      NULL      09092016
1166      481      2016-09-09 06:55:34.737      NULL      09092016
1073      604      2016-09-09 06:41:40.243      NULL      09092016
1072      604      2016-09-09 06:41:40.243      NULL      09092016
0
Comment
Question by:RadhaKrishnaKiJaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41791444
So what is your question:  How to avoid inserting duplicates, how to remove duplicates in the table already, or both?
Looks like whatever feeds @UserID is doing it twice, hence the duplicates when it hits your query.
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 41791447
Thanks for your reply. My question is " How to avoid inserting duplicates"?
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41791453
One of the cases that will be easily solved with a proper PK. I guess you're using an identity column as PK, right?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:RadhaKrishnaKiJaya
ID: 41791454
Yes
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41791456
It would help if you give the column names for your sample data.
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 41791459
StdLogID UserID     StdTimeIn                       StdTimeout    TodaysDate
-----------------------------------------------------------------------------------------------
1139      325      2016-09-09 06:52:28.057      NULL      09092016
 1140      325      2016-09-09 06:52:28.057      NULL      09092016
 1141      325      2016-09-09 06:52:28.057      NULL      09092016
 1154      329      2016-09-09 06:53:38.033      NULL      09092016
 1155      329      2016-09-09 06:53:38.033      NULL      09092016
 1167      481      2016-09-09 06:55:34.737      NULL      09092016
 1166      481      2016-09-09 06:55:34.737      NULL      09092016
 1073      604      2016-09-09 06:41:40.243      NULL      09092016
 1072      604      2016-09-09 06:41:40.243      NULL      09092016
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41791639
Use this:
if not exists
	(select * from LOGINDETAILS  
		where 
			userID = @UserID 
		and TodaysDate =FORMAT(GetDate(),'MMddyyyy')
		and DATEDIFF(SECOND, StdTimeIn,GetDate()) < 60
		and StdTimeIn IS NOT NULL and StdTimeout IS NULL)
begin
	INSERT INTO dbo.LOGINDETAILS 
	(	UserID,               
		StdTimeIn,
		TodaysDate     
	) 
	SELECT 
		@UserID,                                     
		CURRENT_TIMESTAMP,
		FORMAT(GetDate(),'MMddyyyy')
	WHERE
		NOT EXISTS (SELECT * FROM dbo.LOGINDETAILS WHERE UserID=@UserID and StdTimeIn=CURRENT_TIMESTAMP and TodaysDate=FORMAT(GetDate(),'MMddyyyy'))
end

Open in new window

The problem is that your IF statement checks different values from what you insert so you will have to make sure that what you actually insert does not already exist.
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 41791760
Thank you for trying to help me. But it didn't work.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41791825
What exactly didn't work?
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 41794321
Still it is adding the duplicates.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41794348
How can this insert duplicates?
INSERT INTO dbo.LOGINDETAILS 
	(	UserID,               
		StdTimeIn,
		TodaysDate     
	) 
	SELECT 
		@UserID,                                     
		CURRENT_TIMESTAMP,
		FORMAT(GetDate(),'MMddyyyy')
	WHERE
		NOT EXISTS (SELECT * FROM dbo.LOGINDETAILS WHERE UserID=@UserID and StdTimeIn=CURRENT_TIMESTAMP and TodaysDate=FORMAT(GetDate(),'MMddyyyy'))

Open in new window

It would be possible only if the SELECT part returns 2 duplicate rows that don't yet exist in the target table but in this case that is out of the question because you don't SELECT from a table or sub query but you simply return a rowset with one row formed by the variable content and current date values.

Are you sure you used the code I posted? Or is there a trigger that in the background duplicates the insert somehow?
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 41796188
Actually there is no trigger in the background and I just copied the query you sent.

Thanks for trying to help me.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41796210
Still, if you really want to be helped then you need to backup your statements with some data. I would like to see an example of data before and after you used my code in order to find what causes the duplicates, if that really is the case. The only reason I would see is that the values in the table somewhat differ from the ones are inserted at the moment when they are compared in the WHERE clause. This starts to sounds like twilight zone.
0
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41796248
Try this:
IF NOT EXISTS (SELECT 1 
        FROM LOGINDETAILS  
        WHERE userID = @UserID 
              AND DATEDIFF(SECOND, StdTimeIn,GetDate()) < 60
              AND StdTimeout IS NULL)
    INSERT INTO dbo.LOGINDETAILS (UserID,  StdTimeIn, TodaysDate) 
    VALUES (@UserID,CURRENT_TIMESTAMP,FORMAT(GetDate(),'MMddyyyy')

Open in new window

             
But if possible change the PK to a composite of the columns UserID and StdTimeIn. This will avoid the insert of any duplicate record.
0
 

Author Closing Comment

by:RadhaKrishnaKiJaya
ID: 41796937
Hi,

I like the idea of the composite key. I think that would be the best solution to avoid the duplicates.

Thanks
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question