-1

I inserted 5 records in table STAFF and tried creating a new table using STAFF table but I get an error:

Incorrect syntax near the keyword 'SELECT'

Default syntax:

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    [ WHERE ....; ]

My SQL statement

CREATE TABLE STAFF3 AS
    SELECT *
    FROM STAFF

I don't know where I wrong, How could I fix this ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
cher
  • 27
  • 3
  • For **SQL Server**, you need to use `SELECT * INTO STAFF3 FROM STAFF` - [official MS docs on this](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql?view=sql-server-ver16) – marc_s Nov 22 '22 at 16:47
  • If you want to create a new table as data in another table - use this syntax ```sql SELECT column1 ,column2 ,columnN INTO NewTable FROM OldTable; ``` you just can't call `CREATE` in this case – T.S. Nov 22 '22 at 16:48

1 Answers1

2

CREATE TABLE statements cannot use SELECT queries as a data source. Your options are to either use a View (CREATE VIEW As) or a SELECT INTO statement.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794