Post reply

INSERT fails

  • July 23, 2015 at 5:24 am

    #300566

    Hi Clever People

    I have created a table, which is the following.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[BI1_DW_Fact_QMS_Results](

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

    [KeyTestDate] [int] NOT NULL,

    [RecordID] [varchar](2) NULL,

    [ItemNumber] [varchar](35) NULL,

    [Batch/LotNumber] [varchar](25) NULL,

    [DispositionNumber] [int] NULL,

    [SampleID] [varchar](7) NULL,

    [SampleNumber] [int] NULL,

    [TestID] [varchar](7) NULL,

    [ResultNumber] [int] NULL,

    [TestDate] [int] NULL,

    [TestResultTime] [int] NULL,

    [ResultCode] [varchar](1) NULL,

    [NumericTestResult] [decimal](13, 6) NULL,

    [ZeroResult] [smallint] NULL,

    [AlphaTestResult] [varchar](40) NULL,

    [Remark1] [varchar](40) NULL,

    [Remark2] [varchar](40) NULL,

    [TestSequenceNumber] [int] NULL,

    [VoidResult] [smallint] NULL,

    [Code] [varchar](3) NULL,

    [VerificationStatus] [smallint] NULL,

    [UserWhoPerformedVerificaton] [varchar](10) NULL,

    [VerificationDate] [int] NULL,

    [ResultVerificationTime] [int] NULL,

    [WorksheetNumberforResult] [int] NULL,

    [WorkCenterNumber] [int] NULL,

    [ResultStatusCode] [varchar](1) NULL,

    [ActualTestTimeforResult] [decimal](7, 3) NULL,

    [MethodNumber] [varchar](15) NULL,

    [ShopOrderNumber] [int] NULL,

    [LastUsertoChangetheRecord] [varchar](10) NULL,

    [RecordLastChangedDate] [int] NULL,

    [LastMaintenanceTime] [int] NULL,

    [TesterID] [varchar](10) NULL,

    CONSTRAINT [BI1_DW_Fact_QPM_Results_PK] PRIMARY KEY CLUSTERED

    (

    [KeyItem] ASC,

    [KeyTestDate] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    When I try to load this table with data I get the following error:

    Cannot insert the value NULL into column 'KeyTestDate', table 'CompanyDW.dbo.BI1_DW_Fact_QMS_Results'; column does not allow nulls. INSERT fails

    Is there someone that can tell me what I am doing wrong?

    Kind regards

    Fred

  • July 23, 2015 at 5:33 am

    #1814330

    Fred

    The answer's in the error message. You've created the column NOT NULL (which it has to be to form part of a primary key constraint) and therefore you have to provide a value for that column for each row you insert.

    John

  • July 23, 2015 at 5:38 am

    #1814332

    Hi John

    I thought SQL would automatically insert values if it's a PK. Did I think wrong? I will research it, thanks.

  • July 23, 2015 at 5:46 am

    #1814335

    Hi John

    Yes, you are right. I'm just having a blonde moment. There is an IDENTITY key word in the other PK.

    Thanks for showing me the error of my ways.

    Regards

    Fred

  • July 23, 2015 at 6:03 am

    #1814339

    You won't be able to put an IDENTITY property on the KeyTestDate column, only one column in a table is allowed to be an identity.

    Also, if it's a date, shouldn't it be a DATE or DATETIME data type, not an int?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 23, 2015 at 6:03 am

    #1814340

    The identity property on KeyItem doesn't have anything to do with the value of KeyTestDate other than being a composite key. If you want a default value for the KeyTestDate column when a NULL is inserted, you need to define it as having a default value. Here's an example of the syntax:

    KeyTestDate Datetime not null default getdate()

    I also noticed that in your OP you have KeyTestDate defined as an integer. Is this really what you want?

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

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

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