simplifying a query with not writing the column names

Question

0.00/5 (No votes)

See more:

SQL

db.Execute(@"
  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 7:10am

cs101000

Add a Solution

Comments

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sheikh Muhammad Haris · Accepted Answer · 2013-01-02T08:08:00

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.

OR

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

SQL

SELECT c.name + ','  AS column_name 
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE t.name = 'Your_Table_Name'

Reason:
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.

OriginalGriff · Accepted Answer · 2013-01-02T08:07:00

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

simplifying a query with not writing the column names

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0