Using a WITH clause inside SELECT * INTO [closed]

Question

Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers.

This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers.

Closed 3 years ago.

Improve this question

I have the following SQL for which I want to backup the results in to a new table (using SELECT * INTO backup_table)...

 WITH LIST AS 
(SELECT column1 
FROM table1
UNION
SELECT column1
FROM table2
UNION 
SELECT column1
FROM table3
WHERE column1 IS NOT NULL
)

SELECT
 a.column1
 a.column2
 a.column3
FROM table2 a
WHERE a.column6 NOT IN (SELECT field1 FROM LIST)
AND a.column3 ='1'
ORDER BY a.column3

So far I have tried this but get an error:

SELECT * INTO backup_table FROM (    
 WITH LIST AS 
(SELECT column1 
FROM table1
UNION
SELECT column1
FROM table2
UNION 
SELECT column1
FROM table3
WHERE column1 IS NOT NULL
)

SELECT
 a.column1
 a.column2
 a.column3
FROM table2 a
WHERE a.column6 NOT IN (SELECT field1 FROM LIST)
AND a.column3 ='1'
ORDER BY a.column3) a

The errors I get are:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near ')'

I'm using SQL Server 2014 (12.0.6433.1)

When you using sql server, why did you not add an aappropiate tag? — Reporter
– Reporter, Commented Nov 23, 2022 at 11:57
with, similar to a view or a derived table defines a table expression - it is defined at the start of your query and then referenced, you don't define it within a select; create your backup table first, then use that in your main query. — Stu
– Stu, Commented Nov 23, 2022 at 11:58
The SELECT/UPDATE/DELETE/etc statement goes after the WITH not before it. — Thom A
– Thom A ♦, Commented Nov 23, 2022 at 12:03

Dordi · Accepted Answer · 2022-11-23 15:51:36Z

0

 ;WITH LIST AS 
(SELECT column1 
FROM table1
UNION
SELECT column1
FROM table2
UNION 
SELECT column1
FROM table3
WHERE column1 IS NOT NULL
)

SELECT
 a.column1
 a.column2
 a.column3
INTO backup_table
FROM table2 a
WHERE a.column6 NOT IN (SELECT field1 FROM LIST)
AND a.column3 ='1'
ORDER BY a.column3

edited Nov 23, 2022 at 15:51

answered Nov 23, 2022 at 12:30

Dordi

7781 gold badge6 silver badges16 bronze badges

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

3 Comments

MatBailie Over a year ago

Shouldn't even need the sub-query. Just WITH x AS (foo) SELECT a, b, c INTO y FROM x WHERE bar. Also, ORDER BY with SELECT INTO is redundant unless using TOP, etc.

Matt Over a year ago

HI @Dordi Thanks. I'm still getting an error though. "Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon."

Dordi Over a year ago

@Matt, you should add a semicolon ; before WITH, i think you have another statement before WITH, that's why you need to add a semicolon ;

Collectives™ on Stack Overflow

Using a WITH clause inside SELECT * INTO [closed]

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related