RadhaKrishnaKiJaya
asked on
Insert statement is inserting duplicate records
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(),'MMddyyy y')
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
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(),'MMddyyy
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
ASKER
Thanks for your reply. My question is " How to avoid inserting duplicates"?
One of the cases that will be easily solved with a proper PK. I guess you're using an identity column as PK, right?
ASKER
Yes
It would help if you give the column names for your sample data.
ASKER
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
--------------------------
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
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
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.
ASKER
Thank you for trying to help me. But it didn't work.
What exactly didn't work?
ASKER
Still it is adding the duplicates.
How can this insert duplicates?
Are you sure you used the code I posted? Or is there a trigger that in the background duplicates the insert somehow?
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'))
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?
ASKER
Actually there is no trigger in the background and I just copied the query you sent.
Thanks for trying to help me.
Thanks for trying to help me.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
I like the idea of the composite key. I think that would be the best solution to avoid the duplicates.
Thanks
I like the idea of the composite key. I think that would be the best solution to avoid the duplicates.
Thanks
Looks like whatever feeds @UserID is doing it twice, hence the duplicates when it hits your query.