Description	This SQL inserts lines into all Payscale Tables, taking one table as a reference.
Summary
Keywords	Payroll, SQL, Payscale
File / Script Link
Compatibility APM Version
Compatibility SQL Version

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

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

Change HLK-CRUISE with the code of the reference Payscale Table.

Automatically insert lines into all Payscale Tables, taking one table as a reference

Description

SQL