INSERTING DEFAULT VALUES INTO TARGET TABLE

  • Hello

    I need help in inserting records from one table into another.  Sounds simple enough, right ?  Well I'm using a trigger to move the INSERT table into a target table based upon 14 fields in another table.  Lets say if the field PHONE is updated or if a record is added to the first table, then I want this record inserted into a second table.  No problem.  But....I only want populated fields sent to the second table.  If a field in the first table is NULL, then I want to populate the second table with the default value defined in the second table.

    Make sense ?   Any ideas ?

  • Can you write a sub query to insert the defaults?

    INSERT INTO tblA

    (A, B, C)

    SELECT A, B, C

    FROM tblB

    WHERE tblB.D = '?'

  • If you pass a value, (even a NULL) to table, then the DEFAULT set on the table will not fire.  In other words, TableA has [id], [text], [date] with a DEFAULT of '01/01/1900' for the [date].  If you write an INSERT INTO TableA( [id], [text], [date] SELECT x, y, z FROM TableB, and z IS NULL, then the DEFUALT will not work. 

    You probably need to use the ISNULL function. 

    I wasn't born stupid - I had to study.

  • I believe the best way is to replace the values for explicit defaults on the  inserted table using isnull()  but if you still want code independence (I mean if you change a default or add a default you don't have to change trigger code) there is a very clumsy solution which I don't personally recomend but for the fun of it here you go:

    Assuming the detination table has as primary key and identity column named ID

    begin transaction

    insert into table2 default vaules

    if @error <> 0 goto QuitWithRollback

    select @id = scope_indentity()

    insert into table2 ( fld1, fld2, fld3)

    select

      isnull(i.fld1,def.fld1),

     isnull(i.fld2,def.fld2),

     isnull(i.fld3,def.fld3)

    from

         inserted  i

         cross join

         (select * from table2 where id = @id ) def 

    if @error <> 0 goto QuitWithRollback

    delete from table2 where id = @id

    if @error <> 0 goto QuitWithRollback

    commit transaction

    return

    QuitWithRollback:

    if @@trancount > 0 rollback transaction

    This is how it would look with the explicit defaults

    insert into table2 ( fld1, fld2, fld3)

    select

      isnull(i.fld1,'Default1'), -- simpler right ? but code maintenance is a must

     isnull(i.fld2,'Default2'),

     isnull(i.fld3,'Default3')

    from

         inserted  i

     


    * Noel

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply