在不写列名的情况下简化查询 [英] simplifying a query with not writing the column names

问题描述

 db。执行（@   
 INSERT INTO DeletedOrders 
 VALUES（@ 0，@ 1，@ 2，@ 3，@ 4，@ 5，@ 6，@ 7 
，@ 8，@ 9，@ 10，@ 11，@ 12，@ 13，@ 14，@ 15，@ 16，@ 17） 
，AuditNum，qFullOrder.Price，qFullOrder.Status，qFullOrder.PricingTime 
，qFullOrder.WordsNum，qFullOrder.DeliveryTime，qFullOrder.FileName 
，qFullOrder.Lang，qFullOrder.Description，qFullOrder。 Time ，qFullOrder.Field 
，qFullOrder.Subject，qFullOrder.Gender，qFullOrder.FirstName，qFullOrder.LastName 
，qFullOrder.Cell，qFullOrder.Phone，qFullOrder.Email）; 

我有一张19列的桌子。第一个是身份类型。我想执行上面的查询（有18列），而不是将所有这19个列名称放在

（在值部分之前）。虽然第一列是标识我得到这个错误：

查询和表中的列数必须匹配。

解决方案

始终在 INSERT 和 SELECT 投影中使用显式列。即使你不想，也应该。它是一个很好的SQL实践。



只需从对象浏览器中拖放列名即可。



或



您可以使用以下查询获取列列表为你的桌子

  SELECT  c.name + ' ，'  AS  column_name 
  FROM  sys.tables  AS  t 
  INNER   JOIN  sys.columns c  ON  t。 OBJECT_ID  = c。 OBJECT_ID  
  WHERE  t.name = '  Your_Table_Name' 

原因：

对于实例，如果你没有提到列名，那么很长一段时间后如果你在表中添加了一个新列，那么整个映射就行了错了，所有操作都会导致代码破坏。









接受解决方案，如果它对你有帮助。

如果你没有指定列名，那么你必须为表中的每一列提供数据 - 否则它不知道它们要去哪里。问题是，如果您有任何自动列，则无法提供数据，因为这会导致错误...



这不是一个好主意无论如何都要省略列名 - 它似乎对你来说更有用，但如果将来有人改变表格布局，它对你的数据是一个有用的保障。如果您没有指定列，则只会插入数据，并且可能会输入错误的列 - 这将比复制粘贴作业需要更长的时间进行整理，以便将列名称添加到查询中。

db.Execute(@"
  INSERT INTO DeletedOrders 
  VALUES (@0, @1, @2, @3, @4, @5, @6, @7
  , @8, @9, @10, @11, @12, @13, @14, @15, @16, @17)"
  , AuditNum, qFullOrder.Price, qFullOrder.Status, qFullOrder.PricingTime
  , qFullOrder.WordsNum, qFullOrder.DeliveryTime, qFullOrder.FileName
  , qFullOrder.Lang, qFullOrder.Description, qFullOrder.Time, qFullOrder.Field
  , qFullOrder.Subject, qFullOrder.Gender, qFullOrder.FirstName, qFullOrder.LastName
  , qFullOrder.Cell, qFullOrder.Phone, qFullOrder.Email);

I have a table with 19 columns. The first one is of identity type. I want to perform the above query (with 18 columns) without putting all those 19 column names in the statment
(before values part). Although the first column is identity I get this error:
The number of columns in the query and the table must match.

解决方案

Always use explicit columns both in the INSERT and in the SELECT projection. Even if you don''t want to, you should. Its a good SQL practice.

Just drag and drop the column names from the object browser.

OR

you can use the query below to get List of columns for your table

SELECT c.name + ','  AS column_name 
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE t.name = 'Your_Table_Name'

Reason:
For Instance, If you don''t mention column names, then after a long time if you added a new column in your table, then the whole mapping got wrong and all operations would result in breaking your code.




Accept Solution if it helps you.

If you do not specify column names, then you must provide data for each and every column in the table - otherwise it doesn''t know where they are to go. The problem with that is, that if you have any automatic columns, you cannot provide the data because that will cause an error...

It is not a good idea to omit column names anyway - it may seem more work for you, but it is a useful safeguard for your data should anyone change the table layout in future. If you do not specify columns, then data will just be inserted, and could be entered into the wrong columns - which will take a lot longer to sort out than a copy-and-paste job to add the column names to your queries...

这篇关于在不写列名的情况下简化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！