Clever Geek Handbook

Invalid syntax near '('. Pending identifier

I have looked at several other questions of this type and have not found one that helps me solve this problem. I am trying to do the following: I want to create a table for all employees with an assignment target to EmployeeID

be used in several other tables. This table and others work fine. My problem comes up when I try to create a new table based on EmployeeType

so that I can bring up information based solely on employees of a certain type. In the instructions SELECT

for all three tables, I get this error:

Invalid syntax near '('. Pending identifier.

I've googled and googled to resolve this, but nothing I've tried works. What am I missing?

CREATE TABLE Employees
(
    EmployeeID      int             NOT NULL    PRIMARY KEY IDENTITY,
    EmpFirstName    char(50)        NOT NULL,
    EmpLastName     char(50)        NOT NULL,
    EmpAddress      varchar(50)     NOT NULL,
    EmpCity         char(50)        NOT NULL,
    EmpState        char(2)         NOT NULL,
    EmpZipCode      varchar(10)     NOT NULL,
    EmpPhone        varchar(12)     NOT NULL,
    EmpJobTitle     char(30)        NOT NULL,
    EmployeeType    char(30)        NOT NULL,
    Salary          money           NOT NULL,
    HoursPerWeek    int             NOT NULL,
);

CREATE TABLE Collective AS
    (SELECT
        *
    FROM    
        [dbo].[Employees]
    WHERE
        EmployeeID = Employees.EmployeeID
        AND EmployeeType = 'Collective');

CREATE TABLE PaidStaff AS
    (SELECT
        EmployeeID AS ReviewerID,
        EmpFirstName,
        EmpLastName,
        EmpAddress,
        EmpCity,
        EmpState,
        EmpZipCode,
        EmpPhone,
        EmpJobTitle
        Salary,
        HoursPerWeek
    FROM    
        Employees
    WHERE
        EmployeeID = Employees.EmployeeID
        AND EmployeeType = 'PaidStaff');

CREATE TABLE Volunteers AS
    (SELECT
        EmployeeID AS ReviewerID,
        EmpFirstName,
        EmpLastName,
        EmpAddress,
        EmpCity,
        EmpState,
        EmpZipCode,
        EmpPhone,
        EmpJobTitle
    FROM    
        Employees
    WHERE
        EmployeeType = 'Volunteer');
+3


source to share


2 answers


SQL Server has no feature CREATE TABLE .... AS (SELECT ...

. This is simply invalid T-SQL syntax - hence the error.

If you want to create a new table (which doesn't exist yet!) From SELECT

, you need to use this syntax instead:

SELECT
    EmployeeID AS ReviewerID,
    EmpFirstName,
    EmpLastName,
    EmpAddress,
    EmpCity,
    EmpState,
    EmpZipCode,
    EmpPhone,
    EmpJobTitle
INTO 
    dbo.Volunteers    
FROM    
    dbo.Employees
WHERE
    EmployeeType = 'Volunteer';


This one only works if this new table - dbo.Volunteers

- doesn't exist yet .

If you need to insert rows into an existing table, you need to use this syntax:

INSERT INTO dbo.Volunteers (list-of-columns)
    SELECT (list-of-columns)
    FROM dbo.Employees
    WHERE EmployeeType = 'Volunteer';
+3


source


I would say that you should use views for this instead of creating additional tables.



CREATE TABLE Employees
(
    EmployeeID      int             NOT NULL    PRIMARY KEY IDENTITY,
    EmpFirstName    char(50)        NOT NULL,
    EmpLastName     char(50)        NOT NULL,
    EmpAddress      varchar(50)     NOT NULL,
    EmpCity         char(50)        NOT NULL,
    EmpState        char(2)         NOT NULL,
    EmpZipCode      varchar(10)     NOT NULL,
    EmpPhone        varchar(12)     NOT NULL,
    EmpJobTitle     char(30)        NOT NULL,
    EmployeeType    char(30)        NOT NULL,
    Salary          money           NOT NULL,
    HoursPerWeek    int             NOT NULL
);
go
CREATE view Collective AS
    (SELECT
        *
    FROM    
        [dbo].[Employees]
    WHERE
        --EmployeeID = Employees.EmployeeID AND /* this does not do anything */
        EmployeeType = 'Collective');
go
CREATE view PaidStaff AS
    (SELECT
        EmployeeID AS ReviewerID,
        EmpFirstName,
        EmpLastName,
        EmpAddress,
        EmpCity,
        EmpState,
        EmpZipCode,
        EmpPhone,
        EmpJobTitle
        Salary,
        HoursPerWeek
    FROM    
        Employees
    WHERE
        --EmployeeID = Employees.EmployeeID AND /* this does not do anything */
        EmployeeType = 'PaidStaff');
go
CREATE view Volunteers AS
    (SELECT
        EmployeeID AS ReviewerID,
        EmpFirstName,
        EmpLastName,
        EmpAddress,
        EmpCity,
        EmpState,
        EmpZipCode,
        EmpPhone,
        EmpJobTitle
    FROM    
        Employees
    WHERE
        EmployeeType = 'Volunteer');
+2


source






More articles:


All Articles