Sql – Exclude a column using SELECT * [except columnA] FROM tableA

sqlsql servertsql

We all know that to select all columns from a table, we can use

SELECT * FROM tableA

Is there a way to exclude column(s) from a table without specifying all the columns?

SELECT * [except columnA] FROM tableA

The only way that I know is to manually specify all the columns and exclude the unwanted column. This is really time consuming so I'm looking for ways to save time and effort on this, as well as future maintenance should the table has more/less columns.

Best Answer

You can try it this way:

/* Get the data into a temp table */
SELECT * INTO #TempTable
FROM YourTable
/* Drop the columns that are not needed */
ALTER TABLE #TempTable
DROP COLUMN ColumnToDrop
/* Get results and drop temp table */
SELECT * FROM #TempTable
DROP TABLE #TempTable

Related Solutions

Sql – Insert into … values ( SELECT … FROM … )

Try:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2

This is standard ANSI SQL and should work on any DBMS

It definitely works for:

Oracle
MS SQL Server
MySQL
Postgres
SQLite v3
Teradata
DB2
Sybase
Vertica
HSQLDB
H2
AWS RedShift
SAP HANA
Google Spanner

Sql-server – Select columns from result set of stored procedure

Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable.

Declare @tablevar table(col1 col1Type,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar

Best Answer

Related Solutions

Sql – Insert into … values ( SELECT … FROM … )

Sql-server – Select columns from result set of stored procedure

Related Topic