Problem solve Get help with specific problems with your technologies, process and projects.

Insert data into empty table

I have a table (property) with some columns. One of the columns (loan_percent) has real datatype; however, we need to change that column (loan_percent) to numeric (6,4) datatype. I created an empty table (property_copy) with the same structure as the first table, but when I wanted to insert the data from the first table (property) to the second table (property_copy) I got an error. I have some null values in loan_percent column. This is what I did,

Select * into property_copy
From property
Where 1 = 2

And then,

Insert into property_copy
Select property_id,
Convert (numeric (6,4), loan_percent)
From property

Then an error occurred, and I have some null values in the loan_percent column. So I cannot insert any of the data because there are nulls in the loan_percent column. Please tell me how can I insert all of the data, including the null ones? I wish I could delete them, but that is not possible.

You would think property_copy's loan_percent column would have to be null, because that's what the original column is -- it has to be, since it seems to contain some nulls at the moment.

It's a mystery why you got an error message, because a null is a null (maybe your database couldn't convert a real null into a numeric(6,4) null -- just kidding!!)

Okay, we'll have to trap the nulls ourselves, and since we have to keep those rows, we therefore have to provide a value for the loan_percent column in those rows, so let's go with zero.

The SQL coalesce() function is perfect for this:

Insert into property_copy
Select property_id,
Coalesce ( Convert (numeric (6,4), loan_percent) , 0 )
From property

What coalesce() does is evaluate from left to right and return the first non-null value it finds.

So if loan_percent is not null, it gets converted; but if it's null, a zero is used instead.

This should allow the insert to work properly. If you still get an error, you can simply run the following two queries in succession:

Insert into property_copy
Select property_id,
Convert (numeric (6,4), loan_percent)
From property
Where loan_percent is not null

Insert into property_copy
Select property_id,
From property
Where loan_percent is null

Good luck and please write back if this doesn't work.

For More Information

This was last published in March 2001

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.