INSERT fails

Question

INSERT fails

frdrckmitchell7

Ten Centuries

Points: 1351
More actions
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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 2

Hi John

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

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

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