INSERT Fails with a Default Value

  • Hey everyone, I am having some trouble here with an INSERT statement. I know it has to be something simple I am overlooking, or by brain is just on vacation today. I am trying to insert on a column that does not allow NULLS, the default value for this column is 'n/a'.

    Here is my table:

    [font="Courier New"]CREATE TABLE [dbo].[TableA](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Col1] [nvarchar](5) NOT NULL CONSTRAINT [DF_TableA_Col1] DEFAULT ('unknown'),

    [Col2] [nvarchar](5) NOT NULL CONSTRAINT [DF_TableA_Col1] DEFAULT ('unknown'),

    [Col3] [nvarchar](5) NOT NULL CONSTRAINT [DF_TableA_Col1] DEFAULT ('unknown')

    )[/font]

    Here is my insert statement:

    [font="Courier New"]INSERT INTO [dbo].[TableA]

    ([Col1],

    [Col2],

    [Col3])

    SELECT

    [Col1],

    [Col2],

    [Col3]

    FROM [dbo].[TableB];[/font]

    Note: Column 2 of Table B is NULL.

    This is the error message:

    Msg 515, Level 16, State 2, Line 1

    Cannot insert the value NULL into column 'Col2', table 'MyDb.dbo.TableA'; column does not allow nulls. INSERT fails.

    I am prepared to be embarrassed here. What I am doing wrong? Thanks in advanced.

    Regards,

    Jon

  • [Col2] [nvarchar](5) NOT NULL CONSTRAINT [DF_TableA_Col1] DEFAULT ('unknown'),

    You're trying to insert NULLS into a column that does not allow NULLS

    You could add:

    WHERE TableB.Col2 is not null

    same for Col1 & Col3

  • Or you could use COALESCE

    INSERT INTO [dbo].[TableA]

    ([Col1],

    [Col2],

    [Col3])

    SELECT

    COALESCE([Col1],'unknown'),

    COALESCE([Col2],'unknown'),

    COALESCE([Col3],'unkown'

    FROM [dbo].[TableB];

    If you're using the VALUES statement on insert, you can use the default value:

    VALUES

    (DEFAULT,

    'Something',

    'Something)

    But if you really wanted the defaults to kick in, you need to not refer to the columns at all:

    INSERT INTO [dbo].[TableA]

    ([Col1])

    SELECT

    [Col1]

    FROM [dbo].[TableB];

    But if you refer to the columns directly, you need to deal with the values going in.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Dear whats you are doing you are inserting null values from table b to tableA col1 which is not allow you to insert null values if you want to insert default you should leave the column while inserting data in it.

    or make a trigger before creating a trigger you should change null property of col1 table1.It is not posible that you explicit inserting a values to the column and it use default for it.

    **if you are trying to insert default value in this table it whould be throw error becouse col1 accept only 5 charector where default value is more than 5('unknown')

    change the structure of table too.

    A.

    INSERT INTO [dbo].[TableA]

    ([Col2],

    [Col3])

    SELECT

    [Col2],

    [Col3]

    FROM [dbo].[TableB];

    for this solution remove null propery from tableA col1 and use this trigger.

    B.

    CREATE TABLE [dbo].[TableA](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Col1] [nvarchar](7) CONSTRAINT [DF_TableA_Col111] DEFAULT ('unknown'),

    [Col2] [nvarchar](5) NOT NULL CONSTRAINT [DF_TableA_Col222] DEFAULT ('unknown'),

    [Col3] [nvarchar](5) NOT NULL CONSTRAINT [DF_TableA_Col333] DEFAULT ('unknown')

    )

    create trigger trginserttableA

    on tableA

    after insert

    as

    begin

    declare @data varchar(7),@id int

    select @data=col1,@id=id from inserted

    select * from inserted

    if(@data is null)

    begin

    update tablea

    set col1='Unknown'

    where id=@id

    end

    end

    INSERT INTO [dbo].[TableA1]

    ([Col1],

    [Col2],

    [Col3])

    SELECT

    [Col1],

    [Col2],

    [Col3]

    FROM [dbo].[TableB];

    check the result:----

    select * from tablea1

    B is the propre solution for you where you can use original value from tableB and unknown for record which is null.

  • Thank you all. I finally got some sleep after a couple of all-nighters. It's amazing how you over look rather simple things with a lack of sleep. Your suggestions worked and got me back on track. Thanks again - Jon.

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

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