Insert Query.

[Rahul47]

A simple insert query works as : INSERT INTO <table-name> VALUES(val1,val2,val3); and DONE.

Suppose I wanted to insert only into last two columns I will do as : INSERT INTO <table-name>(col2,col3) VALUES(val2,val3);

For less no of columns its not a big deal but what if i have 10 columns and i wanted to add data in only 5 of them.
Like : INSERT INTO <table-name>(col1,col3,col6,col9,col10) VALUES(val1,val3,val6,val9,val10);

Is there any logical or tricky way to reduce this trouble ? Am having a table with 15 columns and sometimes i just need to add data into 7 or 8 columns.

Thanx.

[IIM]

Are you doing it with any programming language like java/php.
If yes,then simpler way is to create data transfer object and set whatever value you want to save and save to all columns.set default value for all columns.If value not set,then data will be inserted with its default value.

[Rahul47]

Are you doing it with any programming language like java/php.

No, am just practicing MySQL in command mode and i was just searching for, if there is any way to do that .

[Reverend Jim]

You can do an insert with fewer columns as long as the missing columns are not required or are auto-increment. For example, if you have the table (and I am defining it with pseudo keywords rather than actual keywords)

Employee
    EmpID       identity (auto-increment) primary key
    LastName    string required
    FirstName   string required
    PriPhone    string required
    AltPhone    string optional

then you could do

INSERT INTO Employee (LastName,FirstName,PriPhone)
       VALUES("Parker","Peter","555-2222")

but not

    INSERT INTO Employee (LastName,FirstName)
       VALUES("Parker","Peter")

In the first case the inserted record would contain

MAX(EmpID)+1, "Parker", "Peter", "555-2222", NULL

[Rahul47]

@Reverend Jim: I see your point, but unfortunately that is not the case here.

Suppose we have some 5 more fields in Employee with optional and required string. Then each time we will have to call INSERT statement.

My point was to insert multiple rows at a time. And I just figured out that we can do that as following.

INSERT INTO table-name (column-name,column-name,column-name)
    VALUES (value,value,value),(value,value,value),(value,value,value);

And that really saves time.

[Reverend Jim]

Well I'll be dipped in (deleted). I've been using SQL for years and I was never aware you could do that.

[IIM]

If that is the case,then i think so you have to insert complete data,if a column data is not required,then insert its default value or null.

Because SQL desn't provide changing of data format.
Refer http://stackoverflow.com/questions/10286620/one-query-to-insert-multiple-rows-with-multiple-columns

@Reverend Jim:- this is not necessay,it depends what is the default value,if default value is not set,then it will contain NULL.

In the first case the inserted record would contain

MAX(EmpID)+1, "Parker", "Peter", "555-2222", NULL

[Rahul47]

Thanx guys . . . . @Jim, @IIM