In IBM-land, the IBM i operating system allows you to override or substitute at runtime one database file for another. This is useful if you want to run a program or CL (Control Language) batch command with different data than usual; for example, using test data in place of production data.

PGM
/* Substitutes QTEMP/MYFILE for HANNA/MYFILE. */
OVRDBF FILE(HANNA/MYFILE) TOFILE(QTEMP/MYFILE)
CALL PGM(HANNA/MYPGM) /* Reads data from QTEMP/MYFILE. */
DLTOVR FILE(HANNA/MYFILE)
ENDPGM

SQL Server does not usually have this capability; but in certain circumstances, it is possible to override one SQL table for another at runtime.

In Kronos custom reports, a table called MYWTKEMPLOYEE holds the list of employees and time period and is a primary data source. When customizing a report or developing a new one, it’s useful to provide your own employee list with time period. The MYWTKEMPLOYEE looks like this.

CREATE TABLE MYWTKEMPLOYEE (
  SESSIONID INT NOT NULL,
  PERSONID INT NOT NULL,
  EMPLOYEEID INT NOT NULL,
  STARTDATE DATETIME NOT NULL,
  ENDDATE DATETIME NOT NULL
);

Sample data can be created from Kronos similar to this query. Here, we are going to use all employees whose last name starts with “A”, have an active timekeeping license, and a date range of 1^st October through 31^st October.

USE WFCDB;
GO
DECLARE @SESSIONID INT = 1;
INSERT INTO MYWTKEMPLOYEE (
  SESSIONID,
  PERSONID,
  EMPLOYEEID,
  STARTDATE,
  ENDDATE
)
SELECT
  @SESSIONID AS SESSIONID,
  P.PERSONID,
  J.EMPLOYEEID,
  CONVERT(DATETIME, N'20190901') AS STARTDATE,
  CONVERT(DATETIME, N'20191001') AS ENDDATE
FROM VP_PERSONV42 AS P
INNER JOIN JAIDS AS J ON (P.PERSONID = J.PERSONID)
WHERE LEFT(P.LASTNAME, 1) = N'A' AND
ISWFCTKEMPLICENSE = 1;

But how do we use our sample data without mucking about with the production MYWTKEMPLOYEE table? It turns out, we can use a common table expression (CTE) to do so, within certain limitations.

Here is an example to pull in time card totals using MYWTKEMPLOYEE as the employee list and date range.

USE WFCDB;
GO
DECLARE @SESSIONID INT;
;WITH MYWTKEMPLOYEE AS (
  SELECT
    @SESSIONID AS SESSIONID,
    P.PERSONID,
    J.EMPLOYEEID,
    CONVERT(DATETIME, N'20190901') AS STARTDATE,
    CONVERT(DATETIME, N'20191001') AS ENDDATE)
  FROM VP_PERSONV42 AS P
  INNER JOIN JAIDS AS J ON (P.PERSONID = J.PERSONID)
  WHERE LEFT(P.LASTNAME, 1) = N'A' AND
  P.ISWFCTKEMPLICENSE = 1
)
SELECT T.*
FROM MYWTKEMPLOYEE AS M -- Uses CTE MYWTKEMPLOYEE instead of table MYWTKEMPLOYEE.
INNER JOIN VP_TOTALS AS T ON (
  M.PERSONID = T.EMPLOYEEID AND
  M.STARTDATE <= T.APPLYDATE AND T.APPLYDATE < M.ENDDATE
)
WHERE M.SESSIONID = @SESSIONID;

In this way, we can develop the query for a Kronos custom report. When we’re satisfied with the results, we simply remove the CTE and use the query as-is in our report. The @SESSIONID variable can remain because that will be one of the report parameters.

So, what are the limitations of this technique?

• It does not work if the tables are schema-qualified. For example, using FROM DBO.MYWTKEMPLOYEE instead of FROM MYWTKEMPLOYEE as we did above. The DBO makes the table lookup more specific.
• Related to above, you cannot create the CTE with it, such as ;WITH DBO.MYWTKEMPLOYEE AS (... because a dotted name is not allowed in a CTE. Similarly, ;WITH [DBO.MYWTKEMPLOYEE] AS(... is a valid CTE name, but SQL Server treats [DBO.MYWTKEMPLOYEE] and [DBO].[MYWTKEMPLOYEE] as separate entities.
• You can’t use it before calling a stored procedure, because common table expressions are not allowed before a stored procedure, ex.

;WITH MYWTKEMPLOYEE AS (...)
EXEC MYSTOREDPROC; -- This does not work.

Microsoft says: “A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.” (As of SQL Server 2019.)

Concluding, if you can working within the narrow requirements, it is possible to substitute an SQL table with another temporarily.