sql - is there a "Wildcard Except" function in MSSQL?

Often I find myself wanting to insert all columns except the IDENTITY column into a table, or select all columns apart from one.

I wonder if there's something in SQL Server like:

SELECT * EXCEPT SomeColumn FROM SomeTable

asked Oct 4, 2019 at 13:22

Geoff Griswald

1,0732 gold badges15 silver badges29 bronze badges

1

No. You need to list your columns out, and remove the one you don't want. If you really don't want to write them all out, purchase a add-on tool that allows the * character to be expanded by a hot key (for example, I use SQL Prompt, and you can Tab the * to have to changed to the name of every column with their relevant object prefix (alias)), and then remove the "offending" column.
– Thom A
Commented Oct 4, 2019 at 13:23
1

No, you need to manually choose your columns, and quite frankly you should do that. SELECT * in any format is bad practice.
– DavidG
Commented Oct 4, 2019 at 13:23
1

Blargh on that WHILE solution on the duplicate.
– Thom A
Commented Oct 4, 2019 at 13:26
1

This sounds like an interesting addition to the SQL syntax but it's always better to be explicit in what you are returning as opposed to implicit. Imaging expecting a result set containing 10 columns (which the id column was excluded from) and then months later someone added 5 more columns to the table. Is your code robust enough to deal with the unexpected columns? Would anyone else's code that consume's the result set be?
– Martin
Commented Oct 4, 2019 at 13:26
Thanks Larnu for the "SQL Prompt" tip. Some of my tables have 300-400 columns and listing out every one for a simple INSERT INTO is a massive pain.
– Geoff Griswald
Commented Oct 7, 2019 at 8:06

Add a comment |

is there a "Wildcard Except" function in MSSQL? [duplicate]