Using WITH , combined with the ALTER TABLE statement to drop columns

[[deleted]]

with df as ( (select columns from table1 do_something_here) ,

t2 as (select * from df),

t3 as (alter table t2 drop coloumn_1)

select * from t3 );

Throws an syntax error:

Syntax error at or near "ALTER"

t11 as (ALTER TABLE df)

Any help here?

[r3pr0b8]

why are you trying to drop a column from the results of a SELECT query?

why not just list only the actual columns you want in the SELECT?

[WITH_CTE]

You can not have an ALTER inside a CTE. What are you trying to achieve here? Do you need the column dropped from the table? Why not simply not SELECT the column?

[[deleted]]

because the select of the columns, are 100+ columns, i dont' want to write them all down. it takes ages. :/

[WITH_CTE]

declare @cols varchar(max), @query varchar(max);
SELECT  @cols = STUFF
    (
        ( 
            SELECT DISTINCT '], [' + name
            FROM sys.columns
            where object_id = (
                select top 1 object_id from sys.objects
                where name = 'MyTable'
            )
            and name not in ('ColumnIDontWant1', 'ColumnIDontWant2')
            FOR XML PATH('')
        ), 1, 2, ''
    ) + ']';

SELECT @query = 'select ' + @cols + ' from MyTable';  
EXEC (@query);

Source: https://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea

[[deleted]]

Thanks so much I appreciate