question

chetanseran avatar image
chetanseran asked

Prevent duplicate insertion on table in parallel process like signal R call

@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
inserttableduplicatesparallelismprocess
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.