So I want to insert 40 values into a table, starting at a particular column. Like this:
INSERT INTO MyTable (1) VALUES (...my forty values...)
Pretending that 0 (zero) indexes the first column, and 1 indexes the second column, the purpose is to skip the (first) column that contained an identity value (since normally you can't insert into an identity column anyway).
The only way I currently know how to solve this problem, is to use highly verbose syntax, like this:
INSERT INTO MyTable (...my forty column names...) VALUES (...my forty values...)
But yuck, who wants to explicitly mention all forty column names, ONLY BECAUSE I'm trying to avoid inserting a value into the first column which contains the identity?
It is best practice to name all of the columns, even if you weren't using identities. One of the most heinous problems an application I have had to work with was that they didn't name columns, then replication needed to add a column and boom, all of the code had to be rewritten.
It is easy to write though, just right click the table in SSMS or QA and say script table as insert... This will enerate a script for you that has all of the columns (without the identity column and timestamp if you have one.) It wouldn't be too hard to fashion one out of a query to information_schema.columns also.
If you feel strongly, I would suggest that you go here and submit this feedback: http://connect.microsoft.com/SQLServer/Feedback. It would be nice in ad-hoc usage to not have to name the columns, though
No comments:
Post a Comment