Insert Query: Why is it a bad idea to not include

Question

I was told at my last position never to do this; it wasn't explained why. I understand it enhances the opportunity for mistakes, but if there are just a couple of columns and I'm confident of their order, why can't I shorthand it and just insert the values in the right order without explicitly matching up the column names? Is there a large performance difference? If so, does it matter on a small scale?

If there's no performance hit and this isn't a query that will be saved for others to view, why shouldn't I?

Thanks in advance.

Answer 1

The reason is to make the code more robust.

Specifying the fields makes the code less dependant on that the table layout stays exactly the same, and also gives you the ability to add fields to the table without the need to change the code as long as you provide default values for the new field.

It also makes it easier to see that the query is supposed to do, without the need to look up the table layout to see where the data will end up.

Answer 2

Also, there's a quick trick if you don't want to type column names out. In management studio, you can set it up so that your result set returns in a CSV (with column headers). CTRL-T.

Then do a quick,

select top 1 * from <tablename>

and copy and paste the column list from the resultset window.