'Can I have multiple INSERT statements following the same WITH statement [duplicate]
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|