0

I am defining a new WITH statement as newStaff, and then I am taking that data and filling it into other tables. It doesn't seem to let me reference my newStaff table more than once with a FROM statement. SSMS tells me newStaff is an invalid object name on the second INSERT statement.

WITH newStaff AS (
  SELECT
    dat.staffnumber,
    dat.firstname,
    dat.middlename,
    dat.lastname,
    dat.suffix,
    dat.sex,
    dat.birthdate,
    dat.race
FROM dataTable dat
WHERE 1=1
 AND dat.staffnumber is not null
)

INSERT INTO newStaffNameTable(
  staffID,
  firstname,
  middlename,
  lastname,
  suffix)
SELECT
  ns.staffnumber,
  ns.firstname,
  ns.middlename,
  ns.lastname,
  ns.suffix
FROM newStaff ns
WHERE 1=1

INSERT INTO newStaffDemographicTable(
  staffID,
  gender,
  birthdate,
  race)
SELECT
  nsdem.staffnumber,
  nsdem.sex,
  nsdem.birthdate,
  nsdem.race
FROM newStaff nsdem
WHERE 1=1
Thom A
  • 88,727
  • 11
  • 45
  • 75
nuth96
  • 11
  • 1
    A Common Table Expression (CTE) is, as it's name suggests, an **expression**; it is part of the statement it is defined in and that's it. – Thom A May 12 '22 at 22:24
  • 2
    You want a temp table (or table variable) if you want to use the results more than once. – Dale K May 12 '22 at 22:26
  • In fact reading the docs would have answered your question `Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a **single** SELECT, INSERT, UPDATE, DELETE or MERGE statement.` – Dale K May 12 '22 at 22:27
  • 3
    No, Common Table Expressions are not perisited between statements. You could make a view instead, persist the data to a temp table or table variable, or repeat the CTE code. – MatBailie May 12 '22 at 22:27
  • 1
    To be honest not sure why this even needs a CTE. It's a really simple single condition `WHERE dat.staffnumber is not null` you could just write it out twice – Charlieface May 12 '22 at 23:57

0 Answers0