1

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

?

Geoff Griswald
  • 937
  • 12
  • 24
  • 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 Oct 04 '19 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 Oct 04 '19 at 13:23
  • 1
    Blargh on that `WHILE` solution on the duplicate. – Thom A Oct 04 '19 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 Oct 04 '19 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 Oct 07 '19 at 08:06

0 Answers0