Why I can't create a new table using simply query CREATE TABLE? [duplicate]

Question

MS SQL SERVER
When I do query

SELECT *
FROM dbo.transactions

it list all rows and colums perfectly.

Problem is when I'm trying to do a new table with CREATE TABLE

CREATE TABLE dbo.all_transactions 
SELECT *
FROM dbo.transactions

or

CREATE TABLE dbo.all_transactions AS
SELECT *
FROM dbo.transactions

Always I get strange error

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'

There is only * next to SELECT. How it could be an "Incorrect syntax"?

I tried changing the table name or using another table, but it doesn't work. I checked the names of the columns are unique. They are unique.

Create table requires the column information syntax ref: learn.microsoft.com/en-us/sql/t-sql/statements/… for your reading pleasure. — Mark Schultheiss
– Mark Schultheiss, Commented Mar 31, 2023 at 19:33
You have to follow the correct syntax. You can't just write whatever, and expect SQL Server to somehow understand what you want. — Eric
– Eric, Commented Mar 31, 2023 at 22:55

Gary_W · Accepted Answer · 2023-03-31 18:09:47Z

2

Suppose you want to create table all_transactions copying the data from transactions Try out this:

SELECT * INTO dbo.all_transactions
FROM dbo.transactions;

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

answered Mar 31, 2023 at 15:35

1,2361 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1