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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Tay JohnsonAuthor Commented:
Can you skip columns not in order in a insert? Can you provide an example?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tay JohnsonAuthor Commented:
The best. Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.