4

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.

Improve this question
3
  • For your edit "hundreds of rows" rather than "hundreds of fields" they were probably looking for you to mention SQLBulkCopy Commented Oct 8, 2011 at 15:24
  • REALLY SORRY, IT WAS HUNDREDS OF FIELDS. ONE MISTAKE, AND MEANING OF QUESTION CHANGED COMPLETELY. SORRY. BEAT ME TO DEATH. Commented Oct 8, 2011 at 15:29
  • for SQLBulkCopy, let me do some research, thanks Commented Oct 8, 2011 at 15:37

4 Answers 4

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

so is it the only way? Can I use any kind of list(with my Controls) to use their values? I am just asking, I don't have any idea about it...thanks
Not really sure what you are asking. The INSERT grammar is here the only options are to either use an explicit column list or not to use one. If you don't use one you need to supply values for all non computed columns as outlined in my answer.
4

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))
Improve this answer

1 Comment

thanks.rather than this can you give me a example by taking Employees as table with FirstName, LastName, Dept as fields. Will be of great help. Still many thanks
3

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, ...);
Improve this answer

Comments

1

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.

Improve this answer

1 Comment

in first ex., are we inserting multiple values to a column. Lets say 4 values in each column?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.