在列名旁边编写带有值的 INSERT 语句？
当编写包含很多列的 INSERT 语句时，最好将值放在列名旁边，就像在 UPDATE 语句中一样。比如：
insert into myTable
set
[col1] = 'xxx',
[col2] = 'yyy',
[col3] = 42
etc...
有什么技巧可以模仿吗？
我想我对此有所了解：
insert into myTable
select
[col1] = 'xxx',
[col2] = 'yyy',
[col3] = 42
etc...
但别名实际上并不与插入表的列相关联，如果有人向表中添加新列，它真的可能会把事情搞砸。有人对如何做到这一点有任何其他想法吗？
When writing an INSERT statement with a lot of columns, it would be nice to have the value next to the column name like in an UPDATE statement. Something like:
insert into myTable
set
[col1] = 'xxx',
[col2] = 'yyy',
[col3] = 42
etc...
Are there any tricks to mimic this?
I thought I was onto something with this:
insert into myTable
select
[col1] = 'xxx',
[col2] = 'yyy',
[col3] = 42
etc...
But the aliases aren't actually being associated with the insert table's columns and if someone added a new column to the table it could really screw things up. Anyone have any other ideas of how one could do this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
您将得到的最接近的结果是在插入中指定列（这将保护您免受添加新列的担忧）并为选择上的值起别名（这为您提供了某种程度的自记录代码）。
The closest you'll get would be to specify the columns on the insert (this will protect you from your concern about a new column being added) and alias the values on the select (which gives you some degree of self-documenting code).
对于大的插入，我做了花哨的（也许是过于挑剔的）布局。一些示例：
如果您非常确定您不会插入列或以其他方式修改正在插入的内容，则此方法有效。它可以在一个屏幕上显示所有内容，并且可以非常简单地选择哪个值进入哪一列。
如果插入的值可能会更改或很复杂（例如 case 语句），我会执行以下操作（除每五个项目外的所有项目都进行缩进）：（
添加 CASE 语句后，我“计算缩进数”以确保所有内容都已排列整齐正确地布置。）
需要付出一些努力才能正确地布置事物，但它可以使维护、支持和后续修改变得更简单。
For big inserts, I've done fancy (and perhaps overfly fussy) layouts. Some examples:
This works if you're pretty darn certain that you wont ever be inserting columns or otherwise modifying what is being inserted. It gets gets everything on one screen, and makes it fairly simple to pick out what value goes into which column.
If inserted values are likely to change or are complex (such as case statements), I do the following (outdent all but every fifth item):
(After adding that CASE statement, I "counted indents" to make sure I had everything lined up properly.)
It takes a bit of effort to get things layed out properly, but it can make maintenance, support, and subsequent modification simpler.
基本上，没有。 SQL INSERT 的语法是列出所有列，然后列出所有值。即使您可以找到一种技巧来按照您想要的方式表达语法，它也将是不可移植的，并且会让下一个人不得不维护您的代码。
如果您想要列到值的可视化映射，请使用上面菲利普的答案中的内容。但请不要花费任何宝贵的时间让您的代码避免标准语法。
Basically, no. The syntax for SQL INSERT is to the list all the columns, then all the values. Even if you could find a trick to express the syntax the way you want it would be non-portable and confusing to the next person to have to maintain your code.
If you want a visual mapping of columns to values use something along the lines of Philip's answer, above. But please don't spend any of your valuable time to make your code avoid the standard syntax.
一旦您想要同时插入多行，使用 2008 风格的行构造函数或使用较旧的
union all语法，您往往会庆幸您不必指定每一行的列名称。2008 行构造函数：
UNION all：
Once you want to insert multiple rows simultaneously, using either 2008 style row constructors, or using the older
union allsyntax, you tend to be thankful that you don't have to specify the column names for every row.2008 row constructors:
UNION all:
我想出的快速解决方案如下：
The quick solution I came up with was the following: