Insert many values in SQL Server table

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.

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 usin开发者_Go百科g 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.

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.

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

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.

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

---

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 insert will fail) you could also define a View with the required subset of columns then you could insert into that without listing the columns explicitly.

---

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))

---

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

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

---

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 INSERT statement e.g.

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).

---

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 article on the subject.