@ThomasRushton If Code not visible please find attached txt file. /* -- Step 1) Create table 2) Insert Record CREATE TABLE #tblRunning ( AutoID INT IDENTITY(1,1) ,PID INT ,PName VARCHAR(50) ,StartTime TIME ,EndTime TIME ,Display INT ,InsertedBy INT ,InsertedDate DATETIME DEFAULT(GETDATE()) ) CREATE TABLE #tblPlayers ( PID INT IDENTITY(1,1) ,PName VARCHAR(50) ) INSERT INTO #tblPlayers ( PName ) SELECT 'Sachin' UNION ALL SELECT 'Virat' UNION ALL SELECT 'Dhoni' UNION ALL SELECT 'Dravid' UNION ALL SELECT 'Jadeja' */ DECLARE @PID INT = 0 ,@PName VARCHAR(50) ='' ,@StartTime TIME ,@EndTime TIME ,@UserID INT = 1 SELECT @PID = PID ,@PName = PName FROM #tblPlayers WHERE PID NOT IN (SELECT PID FROM #tblRunning) ORDER BY NEWID() IF NOT EXISTS ( SELECT PID FROM #tblRunning WHERE (CONVERT(TIME, CONVERT(VARCHAR(20), GETDATE(), 114)) >= CONVERT(TIME, CONVERT(VARCHAR(20), StartTime, 108)) AND CONVERT(TIME, CONVERT(VARCHAR(20), GETDATE(), 114)) < CONVERT(TIME, CONVERT(VARCHAR(20), EndTime, 108))) ) BEGIN SELECT TOP 1 @StartTime = EndTime FROM #tblRunning ORDER BY AutoID DESC SELECT @StartTime = IIF(ISNULL(@StartTime,'')='',CONVERT(TIME, CONVERT(VARCHAR(20), GETDATE(), 108)), @StartTime) INSERT INTO #tblRunning ( PID ,PName ,StartTime ,EndTime ,Display ,InsertedBy ) SELECT @PID ,@PName ,CONVERT(TIME, CONVERT(VARCHAR(20), @StartTime, 114)) AS StartTime ,CONVERT(TIME, CONVERT(VARCHAR(20), DATEADD(SECOND, 32, @StartTime), 114)) AS EndTime ,2 ,@UserID UNION ALL SELECT @PID ,@PName ,CONVERT(TIME, CONVERT(VARCHAR(20), DATEADD(SECOND, 32, @StartTime), 114)) ,CONVERT(TIME, CONVERT(VARCHAR(20), DATEADD(SECOND, 37, @StartTime), 114)) AS EndTime ,1 ,@UserID END SELECT * FROM #tblRunning Execution process of above query is parallel and sometime i got result like below This is first scenario am getting in physical table record is duplicate inserted here milisecond difference in InsertedDate IF NOT EXISTS condion is not satisfied in any case I wants to prevent it AutoID PID PName StartTime EndTime Display InsertedBy InsertedDate 1 3 Dhoni 23:57:51.0000000 23:58:23.0000000 2 1 2020-04-23 23:57:51.600 2 3 Dhoni 23:58:23.0000000 23:58:28.0000000 1 1 2020-04-23 23:57:51.600 3 3 Dhoni 23:57:51.0000000 23:58:23.0000000 2 2 2020-04-23 23:57:51.610 4 3 Dhoni 23:58:23.0000000 23:58:28.0000000 1 2 2020-04-23 23:57:51.610 Second Scenario in parallel process other player is inserted, inserteddate is same IF NOT EXISTS condion is not satisfied in any case AutoID PID PName StartTime EndTime Display InsertedBy InsertedDate 1 3 Dhoni 23:57:51.0000000 23:58:23.0000000 2 1 2020-04-23 23:57:51.600 2 3 Jadeja 23:57:51.0000000 23:58:23.0000000 2 2 2020-04-23 23:57:51.600 3 3 Jadeja 23:58:23.0000000 23:58:28.0000000 1 2 2020-04-23 23:57:51.600 4 3 Dhoni 23:58:23.0000000 23:58:28.0000000 1 1 2020-04-23 23:57:51.600 Third Scenario same like as Second scenario but inserteddate is difference of few milisecond and always InsertedBy is different IF NOT EXISTS condion is not satisfied in any case AutoID PID PName StartTime EndTime Display InsertedBy InsertedDate 1 3 Dhoni 23:57:51.0000000 23:58:23.0000000 2 1 2020-04-23 23:57:51.600 2 3 Dhoni 23:58:23.0000000 23:58:28.0000000 1 1 2020-04-23 23:57:51.600 3 3 Jadeja 23:57:51.0000000 23:58:23.0000000 2 2 2020-04-23 23:57:51.615 4 3 Jadeja 23:58:23.0000000 23:58:28.0000000 1 2 2020-04-23 23:57:51.615 Forth Scenario also not satisfied IF NOT EXISTS condion in AutoID-3 and InsertedDate is milisecond difference AutoID PID PName StartTime EndTime Display InsertedBy InsertedDate 1 3 Dhoni 23:57:51.0000000 23:58:23.0000000 2 1 2020-04-23 23:57:51.600 2 3 Dhoni 23:58:23.0000000 23:58:28.0000000 1 1 2020-04-23 23:57:51.600 3 3 Jadeja 23:57:55.0000000 23:58:27.0000000 2 2 2020-04-23 23:57:51.615 4 3 Jadeja 23:58:27.0000000 23:58:32.0000000 1 2 2020-04-23 23:57:51.615 Please help me to prevent this duplicate entry in Current time my entry always should like below AutoID PID PName StartTime EndTime Display InsertedBy InsertedDate 1 3 Dhoni 23:57:51.0000000 23:58:23.0000000 2 1 2020-04-23 23:57:51.600 2 3 Dhoni 23:58:23.0000000 23:58:28.0000000 1 1 2020-04-23 23:57:51.600 preventduplicate.txt @ThomasRushton
