Write Insert into statement but omit two columns

I am trying to write an insert statement but i would like to omit two columns.  Table A has 8 columns but I want to only insert data into 5 columns.
 Insert into A ( fname, lname, address, city, zip, ssn, gender, race )
Values('Jo', 'Shine','44 darkness street', 'NY', 10047);
Tay JohnsonAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
At this point, I strongly suggest you set up your own test case and just experiment with what is and isn't possible.

If you don't have access to a play database you can use SQL Fiddle or Oracle Live SQL.

I really like Oracle's site:
https://livesql.oracle.com

Registration is free.

Here is a test setup for you to start with:
drop table tab1;

create table tab1(
   col1  varchar2(10),
   col2  varchar2(10),
   col3  varchar2(10),
   col4  varchar2(10)
);

insert into tab1(col3, col1) values('Green','Blue');
insert into tab1(col3, col1, col4) values('Red','Yellow','Purple');

select * from tab1;

Open in new window

0
 
Ryan ChongCommented:
Table A has 8 columns but I want to only insert data into 5 columns.
so specify the columns there...

Insert into A ( fname, lname, address, city, zip, ssn, gender, race )

for example:

Insert into A ( fname, lname, address, city, zip )
Values('Jo', 'Shine','44 darkness street', 'NY', 10047); 

Open in new window


providing other fields accepting null values.
1
 
Mark GeerlingsDatabase AdministratorCommented:
Yes the syntax you posted is legal in Oracle.  That assumes the other three columns do not have NOT NULL constraints.

Have you tried this insert command?  Does it succeed or give you an Oracle error?  If it fails, which Oracle error does it return?
1
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Tay JohnsonAuthor Commented:
Can you skip columns not in order in a insert? Can you provide an example?
0
 
slightwv (䄆 Netminder) Commented:
As long as you specify the column list on the insert side, the columns can be in whatever order you want.  Just adjust the values as well.

This works just fine:
 Insert into A (  city, zip, ssn, gender, race, fname, lname, address )
 Values('NY', 10047,'Jo', 'Shine','44 darkness street');
1
 
slightwv (䄆 Netminder) Commented:
Should add:  The column order only matters when you do not provide the actual column names.

This may run but will put data into the wrong columns:
Insert into A
 Values('NY', 10047,'Jo', 'Shine','44 darkness street');
0
 
Tay JohnsonAuthor Commented:
I do not want to insert data in all the colums, and  the data is coming from another procedure. The columns are not in order that i want to insert data into. It's a i
Insert into ( .)
Values(...)
0
 
slightwv (䄆 Netminder) Commented:
>>I do not want to insert data in all the colums

We understand that and others have shown that as long as you specify the specific columns, it works.  You then asked about column order and as long as you specify the column names, order doesn't matter.

As long as the values provided matches up with the order of the column names, it works.

These work and are the same:
 Insert into A (  city, zip) Values('NY', 10047);
 Insert into A (  zip,city) Values(10047,'NY');

You only get into problems when the columns and values don't match.  This might run or it might generate an error.  If it runs, you end up with wrong data in wrong columns:
 Insert into A (  zip,city) Values('NY',10047);
0
 
Tay JohnsonAuthor Commented:
So, i don't include the colums don't worry but if i do include them it has to match tge order.
Insert into( a, b, c , d,,g, j)
Values(Apple, bear, cat, dog, goat)

The other columns that have letters for animals in order don't worry about them, right
0
 
Mark GeerlingsDatabase AdministratorCommented:
Correct, you do not need to include all column names when you do an insert.  But, then you must list the column names (in any order) that want to insert values into, and you must provide the values for those columns *IN THE SAME ORDER* that you list the column names in.
0
 
slightwv (䄆 Netminder) Commented:
Yes, you don't include the column names but you don't skip over them.

Look at the syntax I've been posting.  I don't have two commas side by side (,,).

It should be:
Insert into( a, b, c , d, g, j)

>>Values(Apple, bear, cat, dog, goat)

For strings, you need to wrap them in single quotes:
Values('Apple', 'bear', 'cat', 'dog', 'goat')

Now the insert you last posted has 6 columns but your values clause has 5 entries.  They need to match.

>>The other columns that have letters for animals in order don't worry about them, right

I don't know what you mean by this.
0
 
Tay JohnsonAuthor Commented:
The best. Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.