SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--
--  Create a stored RefreshInsert procedure which will catch the deadlock 
--  victim abort and restart the insert transaction.
--
CREATE PROCEDURE RF1
       @current_execution INTEGER, @insert_sets INTEGER, @parallel_executions INTEGER, @total_executions INTEGER
AS
BEGIN

DECLARE @startdate DATE
DECLARE @enddate DATE
DECLARE @edate DATE
DECLARE @rangeStart INTEGER
DECLARE @rangeSize INTEGER
DECLARE @range INTEGER

DECLARE @success INTEGER
DECLARE @index INTEGER
DECLARE @div INTEGER
DECLARE @mod INTEGER
DECLARE @skip INTEGER
DECLARE @i INTEGER
DECLARE @rangeSum INTEGER
DECLARE @totRangeSize INTEGER
DECLARE @stmt NCHAR(1000)
DECLARE @orderSql NCHAR(1000)
DECLARE @liSql NCHAR(1000)

DECLARE @ErrorMessage    NVARCHAR(4000)
DECLARE @ErrorNumber     INT
DECLARE @ErrorSeverity   INT
DECLARE @ErrorState      INT
DECLARE @ErrorLine       INT
DECLARE @ErrorProcedure  NVARCHAR(200)

SET @skip = @total_executions/@parallel_executions
SET @div = (@current_execution - 1)/@parallel_executions
SET @mod = (@current_execution - 1) - @div * @parallel_executions
SET @index = @mod*@skip + @div + 1


-- 
-- Get the range for this execution
--
SET @stmt = N'SELECT @sdate = dateadd(day,-1,min(O_ORDERDATE)), @edate = max(O_ORDERDATE) 
		FROM NEWORDERS'
EXEC sp_executesql @stmt,N'@sdate DATE output, @edate DATE output',@startdate output, @enddate output

IF (@total_executions > @parallel_executions)
	BEGIN
	SET @div = (@index-1)/@skip
	SET @mod = (@index-1) - @div * @skip
	SET @rangeSize = datediff(day, @startdate, @enddate)/@parallel_executions + 1
	SET @totRangeSize = @rangeSize
	SET @rangeSum = 0
	SET @rangeStart = @div * @rangeSize
	SET @i = @mod
	while (@i > 0)
		BEGIN
		SET @rangeSize = (@totRangeSize - @rangeSum)/2
		SET @rangeSum = @rangeSum + @rangeSize
		SET @rangeStart = @rangeStart + @rangeSize
		SET @insert_sets = @insert_sets/2
		SET @i = @i - 1
		end
	
	IF (@mod + 1 = @skip) --  last allocation
		SET @rangeSize = @totRangeSize - @rangeSum
	ELSE
		SET @rangeSize = (@totRangeSize - @rangeSum)/2
	IF (@rangeSize < 0)
		SET @rangeSize = 0
	IF (@insert_sets <= 0)
		SET @insert_sets = 1
	end
ELSE
	BEGIN
	SET @rangeSize = datediff(day, @startdate, @enddate)/@total_executions
	SET @rangeStart = @rangeSize * (@index - 1)
	end

SET @startdate = dateadd(day, @rangeStart, @startdate)
IF (@index < @total_executions)
	SET @enddate = dateadd(day, @rangeSize, @startdate)

SET @range = datediff(day, @startdate, @enddate) / @insert_sets

--
--  This handles the case when the max-min/insert_sets is less that 1
--
IF @range = 0
	SET @range = 1

--
-- Generate the two insert statements
--
SET @edate = dateadd(day, @range, @startdate)
SET @orderSql = N'INSERT INTO ORDERS (O_ORDERKEY, O_CUSTKEY, O_ORDERSTATUS, O_TOTALPRICE,
					 O_ORDERDATE, O_ORDERPRIORITY, O_CLERK, O_SHIPPRIORITY, O_COMMENT)
			(SELECT O_ORDERKEY, O_CUSTKEY, O_ORDERSTATUS, O_TOTALPRICE,
					 O_ORDERDATE, O_ORDERPRIORITY, O_CLERK, O_SHIPPRIORITY, O_COMMENT
			 FROM NEWORDERS
			 WHERE O_ORDERDATE > @startdate AND O_ORDERDATE <= @edate)
			option (loop join,MaxDop 1)'
SET @liSql = N'INSERT INTO LINEITEM (L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_QUANTITY,
				L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS,
				L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT)
			(SELECT L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_QUANTITY,
				L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS,
				L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT
			 FROM NEWLINEITEM, NEWORDERS
			 WHERE L_ORDERKEY = O_ORDERKEY AND O_ORDERDATE > @startdate AND 
					O_ORDERDATE <= @edate)
			option (loop join,MaxDop 1)'

--
--  Loop through the order keys inserting sets into the 
--  ORDERS and LINTEITEM tables
--
WHILE @startdate < @enddate
	BEGIN
  	--
  	--  Insert into ORDERS and LINEITEM tables
  	--
  	INSERT_TRANS:
	SET @success = 1
  	BEGIN TRANSACTION

	BEGIN TRY
    		EXEC sp_executesql @orderSql, N'@startdate DATE, @edate DATE', @startdate, @edate
    		EXEC sp_executesql @liSql, N'@startdate DATE, @edate DATE', @startdate, @edate
		select @liSql
		select @orderSql
		select @startdate, @edate
	END TRY
	BEGIN CATCH
		SET @success = 0
		IF (error_number() = 1205) 	-- deadlock victim
			BEGIN
			PRINT 'Insert deadlock - restarting RF1'
			IF (@@trancount>0)
				ROLLBACK TRANSACTION
			END
		ELSE
			BEGIN			-- not a deadlock
			PRINT ERROR_MESSAGE()
			SELECT 	@ErrorNumber = ERROR_NUMBER(),
			        @ErrorSeverity = ERROR_SEVERITY(),
			        @ErrorState = ERROR_STATE(),
			        @ErrorLine = ERROR_LINE(),
			        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

			IF (@@trancount>0)
				ROLLBACK TRANSACTION
			RAISERROR 
			        (
			        @ErrorMessage, 
			        @ErrorSeverity, 
			        1,               
			        @ErrorNumber,    -- parameter: original error number.
			        @ErrorSeverity,  -- parameter: original error severity.
			        @ErrorState,     -- parameter: original error state.
			        @ErrorProcedure, -- parameter: original error procedure name.
			        @ErrorLine       -- parameter: original error line number.
			        );
			END
	END CATCH

	IF (@success = 0)			-- deadlock - redo the inserts
		GOTO INSERT_TRANS

	COMMIT TRANSACTION
  
  	SET @startdate = @edate
  	SET @edate = dateadd(day, @range, @edate)

  	IF (@edate > @enddate)
		SET @edate = @enddate

	END
END

GO


