Insert data into empty table

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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,
0
From property
Where loan_percent is null

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

For More Information


This was first published in March 2001

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top