Click here to Skip to main content
11,924,487 members (52,310 online)
Rate this:
Please Sign up or sign in to vote.
  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.
Posted 2-Jan-13 8:10am
Member 9581488 2-Jan-13 13:40pm
do you have your Identity column's Identity Increment? If you want to insert data without specifying your columnNames use:
insert into tablename(value1,value2,...., so on)
but data types must match.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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.


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

SELECT + ','  AS column_name 
FROM sys.tables AS t
WHERE = 'Your_Table_Name'

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.
cs101000 2-Jan-13 14:36pm
Thank you! Useful query it is.
Sheikh Muhammad Haris 2-Jan-13 18:34pm
Welcome :)
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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...
cs101000 2-Jan-13 14:34pm
Thank you! I see that it's a risky practice. I will take your advice.However it's a little more than a copy-paste in my scenario :-)
OriginalGriff 2-Jan-13 14:37pm
It always is! :laugh:

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web03 | 2.8.151125.3 | Last Updated 2 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100