Incorrect syntax near 'as' incorrect syntax near ')'

Question

1.00/5 (1 vote)

See more:

I am trying to name my select statements as one table under 'hotels'.
What am I doing wrong

What I have tried:

with hotels
as (
select * FROM dbo.hotel_2018
union
select * FROM dbo.hotel_2019
union
select * FROM dbo.hotel_2020)

Posted 12-Jan-22 12:58pm

Rahab Lawshea

Updated 12-Jan-22 15:16pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Luc Pattyn · Accepted Answer · 2022-01-12T15:16:00

Solution 1

Your SQL statement is incomplete, you still have to do something with those hotels.
Example:

T-SQL

with hotels
as (
select * FROM dbo.hotel_2018
union
select * FROM dbo.hotel_2019
union
select * FROM dbo.hotel_2020 ) select * FROM hotels

doesn't give me any syntax errors.

:)

PS: it isn't considered good practice to split data over several tables all having the same structure. Databases don't mind large tables, you probably should have put all records in a single table right from the start.

Posted 12-Jan-22 15:16pm

Luc Pattyn

Updated 12-Jan-22 15:31pm

v4

Comments

Richard Deeming 13-Jan-22 4:05am

Especially when SQL Server already supports Partitioned Tables[^]. :)

Rahab Lawshea 13-Jan-22 10:30am

This is my current SQL statement.

with hotels as
(
select * FROM dbo.hotel_2018
union
select * FROM dbo.hotel_2019
union
select * FROM dbo.hotel_2020
)
select *
from hotels

When I run the statement above I get no syntax errors. However, when I run the statement below I get "Invalid object name 'hotels'.

select * from hotels

I will probably end up combining the tables as you mentioned. I am new to SQL so I am just trying different things.

Herman<T>.Instance 13-Jan-22 10:47am

;WITH hotels AS ()