INSERT DEFAULT 1

[YipYeppp]

Hi,

I have to make an insert from a table to another, example:
Insert into table1 (select * from table2)

If a field of table2 is empty or NULL, i'd like it to automatically insert the DEFAULT value.

I've tried it, but it doesn't work, the only way it inserts the DEFAULT is when i specify it in the INSERT statement, but i can't make it this way, i have to make a SELECT * because my tables have over than 400 fields.

Can you help me?

Thanks!

 

[SantaMufasa]

Yip,

Following your "Insert into table1 (select * from table2);", what would be the harm in issuing the command:

UPDATE table1 SET <column_name> = <default_value>
WHERE <column_name> IS NULL:

Would this not accomplish your objective?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:38 (08Dec04) UTC (aka "GMT" and "Zulu"),
@ 15:38 (08Dec04) Mountain Time

 

[YipYeppp]

Dear Santa Mufasa,

I can't do it this way, because the wan't to have the exact time when the row is inserted, date+hour+minute to do some statistics. So i have to insert the default value exactly at the same time than the row. The table will have more than 300 000 rows.

Do you know the way to do it?

Thanks..

 

[lewisp]

Place a DEFAULT value on the columns of your tables using the ALTER TABLE command.

 

[YipYeppp]

My table already have a DEFAULT value.
But with the insert into ... (select * ...), if the field of the source table is null, it inserts the null value. And i want it to insert the default value, because we have to have the EXACT time when the row is inserted.

 

[james777]

You can do it this way , if you include column names.,.

insert into table1 select col1, col2, nvl(col3,sysdate) from table2..

 

[SantaMufasa]

James,

You are correct, but what Yip is trying to avoid is explicitly listing his 400 columns involved in each table.

By the way, Yip, I'll bet half of my paycheck that you have a significant number of columns that are disobeying First Normal Form, relational databases' first rule of quality table design. You will probably tell us that the table design is a legacy from someone else's creation, but what better time than the present to fix the problem? Think New Year's Resolutions. If you tell us the categories of data that compose those 400 columns, I'm sure we can suggest nice, easy ways to fix your 1NF problems.

But, getting back to your immediate issue at hand, James is correct...if you must make your functionality happen, then explicitly listing the columns of your 400 that do not have DEFAULT specifications may be your only/best way to achieve your results.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:38 (09Dec04) UTC (aka "GMT" and "Zulu"),
@ 10:38 (09Dec04) Mountain Time

 

[YipYeppp]

I know, but this is a really BIG database, for the government of quebec, with over than 400 tables, 10 millions of rows, 3 DBA's, 10 analysts, 2 architechs, etc..
And we dont want to list all the columns in the insert statement.. I was wondering if a functionnality exists to insert the default value when taking rows from the staging tables to the production tables, by making a "select *". I think it doesn't exists finally.. i will talk with the DBA about this..

Thanks a lot guys and girls..

 

[gunjan14]

Hi Yip,

Long shot.... But if you use PL/SQL, then you can create the insert statement on the fly and use it. Just pass the insert table and selection table name and execute it
Here is an example

create or replace procedure run_insert
(
    ins_table in varchar2,
    sel_table in varchar2
)
as
    l_col_ins varchar2(32000);
    l_col_sel varchar2(32000);
    l_sep     varchar2(2);
    l_def     number := 5;
begin
    for r in (select column_name,data_default from user_tab_columns where table_name = upper(ins_table)) loop
        l_col_ins := l_col_ins||l_sep||r.column_name;
        if r.data_default is not null then
            l_col_sel := l_col_sel||l_sep||'nvl('||r.column_name||', '||r.data_default||')';
        else
            l_col_sel := l_col_sel||l_sep||r.column_name;
        end if;
        l_sep := ','
    end loop;
    execute immediate 'insert into '||ins_table||' ('||l_col_ins||') select '||l_col_sel||' from '||sel_table;
end;
/

Now you can just use it like this

    exec run_insert('INSERT_TABLE','SELECT_TABLE');

Hope this helps

Gunjan

 

[gunjan14]

Hi,

Sorry missed this point that this case only works if the column names are same. Thats why I said its a long shot..

Gunjan

 

[YipYeppp]

Hey, gunjan14, are you a genius?

I try this right now with my test tables, and i will tell you if it work..

thanks!

Fanny

 

[YipYeppp]

Dear Gunjan14,

It works with my test tables, i am really happy. I think that your procedure would be very helpful to many programers. We will now test the perforamnce with a big table to see if it takes too much time, and i think that we will use it. Anyway, i will certainly reuse it in a few other future scripts!

Thanks a lot for your precious help!

 

[gunjan14]

Hi Yip,

Thanks for the star and the accolade. And no, I am not genius... . Anyway glad to be of help. Do let us know about the performance on the big tables.

Gunjan

 

[taupirho]

Yip, since recording the time of the insert is important to you why not add an extra date column to table1. Then do your insert like:

insert into table1 select table2.*, sysdate from table2

Thus capturing the time of insert then simply do an update to set your default columns as advised by Dave.

 

[YipYeppp]

Hi,

I can't use your way tomreid, because the sysdate field will not always be the last, year after year we have to add some fields to the table. I found the solution finally: use a trigger, like this:

BIR means BEFORE INSERT REPLACE.

--D2_BIR_01_TABLE1--
CREATE OR REPLACE TRIGGER D2_BIR_01_TABLE1
BEFORE INSERT
ON D2_TABLE1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
:NEW.TABLE1_S_DATE_DER_MAJ := sysdate;
END;
/


Best Regards and Merry Christmas to Everybody!