1 Description | 2 SQL

Summary	This SQL inserts lines into all Payscale Tables, taking one table as a reference.
Keywords	Payroll, SQL, Payscale

Description

This SQL inserts lines into all Payscale Tables, taking one table as a reference.

Line by line, it inserts new lines for every other Payscale Table, copying the line names from the reference table and not updating existing lines.

SQL

Before running the insert statement change the HLK-CRUISE value with the code of the reference Payscale Table you are using.

DECLARE @PT VARCHAR(32) = 'HLK-CRUISE',@CT VARCHAR(32);
DECLARE @SequenceNo INT, @LineNum INT, @CurTable INT, @RefTable INT;
Set @RefTable = (Select SEQNO from PWPSC000 where REGULATIVECODE = @PT);
Set @LineNum = (Select MAX(Line) From PWPSC_RATES_LINES where SeqNo = @RefTable);

While @LineNum is not null
BEGIN
	Print 'Adding line '+Convert(varchar(3),@LineNum)+' to:';
	Set @CurTable = (Select MAX(t.SEQNO) from PWPSC000 t where t.SEQNO != @RefTable and t.SEQNO not in (Select SeqNo from PWPSC_RATES_LINES where Line = @LineNum));
	While @CurTable is not null
	Begin
		Set @CT = (Select REGULATIVECODE from PWPSC000 where SEQNO = @CurTable);
		Print @CT;
		EXEC dbo.sp_GenerateSeqNo 'PWPSC_RATES_LINES', @SequenceNo OUT;
		Insert into PWPSC_RATES_LINES (LineId,SeqNo,Line,[NAME])
		Select 
			@SequenceNo as LineId,
			@CurTable as SeqNo,
			@LineNum as Line,
			[NAME]
		From PWPSC_RATES_LINES where SeqNo = @RefTable and Line = @LineNum
		Set @CurTable = (Select MAX(t.SEQNO) from PWPSC000 t where t.SEQNO != @RefTable and t.SEQNO not in (Select SeqNo from PWPSC_RATES_LINES where Line = @LineNum) and t.SEQNO<@CurTable);
	End;
	Set @LineNum = (Select MAX(Line) From PWPSC_RATES_LINES where SeqNo = @RefTable and Line<@LineNum);
END;
GO

Reports, Views and Similar

Insert same rate lines into all Payscale Tables

Description

SQL