SQL '(' 附近的语法不正确。需要 ID

Incorrect syntax near '('. Expecting ID

提问by Krystianya

I've looked through several other questions of this type and have not found one that will help me resolve this issue. What I'm trying to do is this: I want to create a table for all employees for the purpose of assigning an EmployeeIDthat will be used across several other tables. That table and the others work fine. My issue arises when I try to create a new table based off the EmployeeTypeso I can bring up information based solely on employees of a specific type. On the SELECTstatement for all three of the tables I get this error:

我已经查看了其他几个此类问题，但没有找到可以帮助我解决此问题的问题。我想要做的是：我想为所有员工创建一个表，以便分配一个EmployeeID将在其他几个表中使用的表。那张桌子和其他桌子工作得很好。当我尝试基于 来创建一个新表时，我的问题就出现了，EmployeeType这样我就可以仅根据特定类型的员工来调出信息。在SELECT所有三个表的语句中，我收到此错误：

Incorrect syntax near '('. Expecting ID.

'(' 附近的语法不正确。需要 ID。

I've googled and googled for how to resolve it, but nothing I've tried is working. 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');

回答by marc_s

SQL Server doesn't have a CREATE TABLE .... AS (SELECT ...feature. That just isn't valid T-SQL syntax - therefore the error.

SQL Server 没有CREATE TABLE .... AS (SELECT ...功能。这只是无效的 T-SQL 语法 - 因此是错误。

If you want to create a new table(which doesn't exist yet!) from a SELECT, you need to use this syntax instead:

如果要从 a创建新表（尚不存在！）SELECT，则需要改用以下语法：

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

This onlyworks if that new table - dbo.Volunteers- does not exist yet.

这只是-如果新表的工作原理dbo.Volunteers-还不存在。

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

如果需要向现有表中插入行，则需要使用以下语法：

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

回答by SqlZim

I would say you should be using views for that instead of creating extra 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');