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

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.