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 EmployeeID that 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 EmployeeType so I can bring up information based solely on employees of a specific type. On the SELECT statement for all three of the tables I get this error:
Incorrect syntax near '('. Expecting 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');2 Answers
SQL Server doesn't have a CREATE TABLE .... AS (SELECT ... feature. That just isn't valid T-SQL syntax - therefore the error.
If you want to create a new table (which doesn't exist yet!) from a 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 only works if that new table - dbo.Volunteers - does not exist yet.
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';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');
