在 SQL Server 表中插入多个值

Insert many values in SQL Server table

提问by Sandy

I don't know but it may be no sense question to many of you, but I was asked in an interview and I really got confused as I have just started working on SQL Server. If I have hundreds of fields to be inserted into a SQL Server database in a single step, do I need to write every column name in my SQL command.

我不知道，但对你们中的许多人来说，这可能是一个毫无意义的问题，但我在一次采访中被问到，我真的很困惑，因为我刚刚开始使用 SQL Server。如果我在一个步骤中将数百个字段插入到 SQL Server 数据库中，是否需要在我的 SQL 命令中写入每个列名。

INSERT into Database(field1, field2,...field100) VALUES(val1, val2,...val3)

I am getting these values from textboxes and comboboxes and similar controls. Is it the only way to do this or is there a easy way of doing this? I don't have a great knowledge of SQL Server. I am trying to learn it. I am using C# 4.0, SQL Server 2008 Express.

我从文本框和组合框以及类似控件中获取这些值。这是唯一的方法还是有一种简单的方法可以做到这一点？我对 SQL Server 不太了解。我正在努力学习它。我使用的是 C# 4.0、SQL Server 2008 Express。

EDIT

编辑

Let me explain the Interveiw Question. To be precise, they asked me that, they had a form to filled in by a user (in winforms). That form was divided into multiple Tab Pages(5-6) in a Tab Control. Now they wanted to feed the data in the database by a single click means, they did not want to send the data page by page. Rather they wanted to feed the data by a single click of a button. Either complete data or none.

让我解释一下面试问题。准确地说，他们问我，他们有一个用户填写的表格（在 winforms 中）。该表单被分成多个 Tab Pages(5-6) 在一个 Tab Control 中。现在他们想通过单击的方式来馈送数据库中的数据，他们不想逐页发送数据。相反，他们希望通过单击一个按钮来提供数据。要么完整数据要么没有。

What I answered is, I will do it through transaction. For clarity i will frame an Insert command for every page and Execute them in a transaction. If succeed Commit otherwise rollback.

我的回答是，我会通过交易来做。为清楚起见，我将为每个页面构建一个插入命令并在事务中执行它们。如果成功提交否则回滚。

But the immediate reply was, "Transaction is fine, but will you write all the textboxes value in insert command?"

但立即的回复是，“交易很好，但你会在插入命令中写入所有文本框的值吗？”

I said if something is null I will ignore, but all the time values can not be null as different users will be filling the form in a different way. So it is better to include all textboxes value (or sql parameters) in our SQL Command.

我说如果某些内容为空，我会忽略，但所有时间值都不能为空，因为不同的用户将以不同的方式填写表单。所以最好在我们的 SQL 命令中包含所有文本框值（或 sql 参数）。

But the question was again same, "Will you be writing all the textboxes values if at all they count to 100s or 200s?"

但问题又是一样的，“如果所有的文本框值都计算到 100 或 200，你会写出所有的值吗？”

With a confused mind, my answer was "yes".

带着疑惑，我的回答是“是”。

And they did not look satisfy.

他们看起来并不满意。

That's the reason I asked in 1 comment. Can it be done with a list. I mean, if I have all the controls added into a list, can we use that list in anyway? But now I feel, we can not.

这就是我在 1 条评论中询问的原因。可以用列表来完成吗？我的意思是，如果我将所有控件都添加到一个列表中，我们还能使用该列表吗？但现在我觉得，我们不能。

And ya they kicked me out of the selected candidate list :-)) LOL, never mind.

是的，他们把我踢出了选定的候选人名单 :-)) 哈哈，没关系。

采纳答案by Martin Smith

If you are inserting all the insert-able columns in the table (i.e. not computed, identity, rowversion) columns then you can omit the explicit column list and just do

如果您在表中插入所有可插入的列（即非计算、标识、行版本）列，那么您可以省略显式列列表，只需执行

INSERT into YourTable
Values(val1, val2,...val3) /*Needs to be in order as per table definition*/

If you are inserting explicit values for only a subset of the columns then you need to list all columns explicitly.

如果您只为列的一个子集插入显式值，那么您需要显式列出所有列。

Assuming the columns left out all are nullable or have a default defined (if not the insertwill fail) you could also define a View with the required subset of columns then you could insert into that without listing the columns explicitly.

假设所有被排除的列都是可为空的或定义了默认值（如果不是，insert则将失败），您还可以定义具有所需列子集的视图，然后您可以插入其中而不显式列出列。

回答by Allan Chua

Try to use this very simple way to make a batch insert of records to a table:

尝试使用这种非常简单的方法向表中批量插入记录：

 INSERT INTO (TABLE NAME)
 (COLUMN NAMES)
 SELECT (FIELDS SHOULD MATCH THE COLUMN NAMES PROVIDED) 
 FROM (SOURCE(Could be variable or another table))

回答by fabulaspb

If you want insert hundreds rows in one step you could do it in next way (SQL server 2008):

如果您想在一个步骤中插入数百行，您可以采用下一种方式（SQL Server 2008）：

INSERT INTO TABLENAME VALUES
(fieldValue, fieldValue, ...),
(fieldValue, fieldValue, ...),
(fieldValue, fieldValue, ...);

回答by onedaywhen

Because you admitted to being confused, which is to be expected in an interview, I suspect the answer they were look for was:

因为你承认自己很困惑，这在采访中是可以预料的，我怀疑他们正在寻找的答案是：

(reducing the insert from 'hundreds' to four rows of three columns, just to make it easier to write out in full):

（将插入从“数百”减少到四行三列，只是为了更容易完整地写出）：

INSERT INTO Table1 (field1, field2, field3) 
   VALUES (1, 2, 3), 
          (1, 2, 4), 
          (2, 1, 9), 
          (2, 3, 8);

In other words, I rather think the question was to the effect of, "Do you need to repeat the column names for each row inserted?" to which the answer is no.

换句话说，我更愿意认为问题在于“您是否需要为插入的每一行重复列名？” 答案是否定的。

Another thought: perhaps they wanted you to point out the relative merits of omitting the column names entirely from the INSERTstatement e.g.

另一个想法：也许他们希望您指出完全从INSERT语句中省略列名的相对优点，例如

INSERT INTO Table1 
   VALUES (1, 2, 3), 
          (1, 2, 4), 
          (2, 1, 9), 
          (2, 3, 8);

The answer to this one is there is that relying on implicit order increases the risk of getting an error, either an obvious one (insert fails) or a more subtle one (insert succeeds but values end up in the wrong columns due to SQL's implicit type conversion language feature).

这个问题的答案是依赖隐式顺序会增加出现错误的风险，无论是明显的错误（插入失败）还是更微妙的错误（插入成功但由于 SQL 的隐式类型，值最终出现在错误的列中）转换语言功能）。

---

---

If you are talking about hundreds of rows (rather than columns) then they may have been hinting at table-valued parameters e.g. Erland Sommarskog's articleon the subject.

如果您谈论的是数百行（而不是列），那么它们可能一直在暗示表值参数，例如Erland Sommarskog关于该主题的文章。