none
how to set a default value of 0 (zero) for a money column when the external data column is null?

    Question

  • I have to import data from a text file to a table in my (sql Server 2000) DB.  I want to set a default value of 0 (zero) for a money column.  I went into the table design and set the Default value or binding to (0) and not allow nulls.  When I try to import a null value I get an error that null are not allowed.  If I allow null on this column then the empty column does not get a default value of 0 (or 0.00).  How can I set this default value for this money column?

    Thanks

     


    Rich P
    Tuesday, August 09, 2011 8:06 PM

Answers

  • Not sure exactly what your trying to get at but I hope this helps. If you can create your insert statement dynamically then altering this a little should get the job done.

    create table bob(
    one varchar(100),
    two varchar(100))

    Declare @value1 varchar(100), @value2 varchar(100)

    Set @value1 = null
    Set @value2 = 'hey'

    insert into bob (one, two) values (ISNULL(@value1,0), ISNULL(@value2,0))

    select * from bob


    Pérez
    Tuesday, August 09, 2011 9:33 PM

All replies

  • Are you using -K switch in the bcp utility if so, please remove it and try again... 

    check the below link for more details on how to insert default values using bcp or bulk insert 

    http://msdn.microsoft.com/en-us/library/ms187887.aspx


    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, August 09, 2011 8:13 PM
  • Actually, I decided to add a trigger (instead of trigger), but the trigger does not appear to be firing.  Here is a sample of my trigger --- what is required to make it fire?

    CREATE TRIGGER [t_DssDetail3_Insert]
       ON  DssDetail3
       Instead Of Insert
    AS
    BEGIN
    insert into dssdetail3(
    ShipToFax,
    LineItemAmtPaid,
    TotalInvMiscCharges,
    ShipToPhone)

    select
    ShipToFax,
    case when LineItemAmtPaid is null then 0 else LineItemAmtPaid end,
    TotalInvMiscCharges,
    ShipToPhone
    from inserted
     
    END
    return

     


    Rich P
    Tuesday, August 09, 2011 8:23 PM
  • Trying to fire a trigger for each and every row inserted through BCP is not a good idea.. it will reduce the performance of BCP utility...

    but if you want to go in that way add the switch -h "FIRE_TRIGGERS"

    for more information check the link

    http://msdn.microsoft.com/en-us/library/ms187640.aspx


    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, August 09, 2011 8:28 PM
  • Trying to fire a trigger for each and every row inserted through BCP is not a good idea.. it will reduce the performance of BCP utility...

    but if you want to go in that way add the switch -h "FIRE_TRIGGERS"

    I'm not using bcp.  I added the trigger to the table in query analyzer (create trigger ...) .  how do I make it fire?

     


    Rich P
    Tuesday, August 09, 2011 8:36 PM
  • ---->I have to import data from a text file to a table in my (sql Server 2000) DB.  I want to set a default value of 0 (zero) for a money column. 

    How are you doing the above Job without BCP / BULK INSERT....

    --->I added the trigger to the table in query analyzer (create trigger ...) .  how do I make it fire?

     

    Try to do a insert statement on the statement on the table DssDetail3 with the LineItemAmtPaid as NULL values and check if the NULL values are inserted.

     

    IF that is not the case, check if the trigger is enabled or disabled... if it is disabled by any chance enable it 

    to check if the trigger is disabled or enabled use the code below 

    SELECT
    CASE OBJECTPROPERTY(OBJECT_ID('trigger name goes here'), 'ExecIsTriggerDisabled')
    WHEN 0 THEN 'ENABLED'
    ELSE 'DISABLED'
    END
    


    To enable the trigger use the below TSQL 

    ENABLE Trigger <your_trigger_name> ON <table>;
    GO
    
    



    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, August 09, 2011 8:44 PM
  • Not sure exactly what your trying to get at but I hope this helps. If you can create your insert statement dynamically then altering this a little should get the job done.

    create table bob(
    one varchar(100),
    two varchar(100))

    Declare @value1 varchar(100), @value2 varchar(100)

    Set @value1 = null
    Set @value2 = 'hey'

    insert into bob (one, two) values (ISNULL(@value1,0), ISNULL(@value2,0))

    select * from bob


    Pérez
    Tuesday, August 09, 2011 9:33 PM