Insert many values in SQL Server table

Question

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

Answer 1

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.

Answer 2

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