Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Insert Statement

    Does anyone see anything wrong with this Insert

    Code:
    INSERT INTO dbo.Report
                          (LossType, Loss, Area)
    SELECT     [Loss Type], Loss, Area
    FROM         dbo.[Main Table]
    I keep getting does not alow nulls insert fails

    this is the Report Table
    Code:
    CREATE TABLE [Report] (
    	[Incident Report No] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Date] [datetime] NULL ,
    	[Time] [datetime] NULL ,
    	[Investigators Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Reported by] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Law Enforcement Agency] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Name of Officer] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Evidence Seized] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Associated Reports] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Corrective Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Corrective Action] DEFAULT (N'YES'),
    	[Comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_I/RDocument] DEFAULT (N'SCANNED REPORT'),
    	[TypeOfIncident] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Exclusion] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Exclusion] DEFAULT (N'NO'),
    	[86_D] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_86_D] DEFAULT (N'NO'),
    	[Loss] [money] NULL CONSTRAINT [DF_Report_Loss] DEFAULT (0.0000),
    	[LossType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Area] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Action/Incident] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Security/GC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Security/GC] DEFAULT (N'GC'),
    	CONSTRAINT [PK_Report] PRIMARY KEY  CLUSTERED 
    	(
    		[Incident Report No]
    	)  ON [PRIMARY] 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    and the [Main Table]


    Code:
    CREATE TABLE [Main Table] (
    	[ID] [int] IDENTITY (1, 1) NOT NULL ,
    	[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Date] [datetime] NULL ,
    	[Inspector] [int] NULL ,
    	[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Violation Type] [int] NULL ,
    	[Loss] [money] NULL ,
    	[Loss Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Action Type] [int] NULL ,
    	[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	CONSTRAINT [PK_Main Table] PRIMARY KEY  CLUSTERED 
    	(
    		[ID]
    	)  ON [PRIMARY] 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

  2. #2
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Your insert does not supply a value for the column [Incident Report No] which is defined as NOT NULL and does not have a default value.

    (Column names with spaces... Shudder...)

  3. #3
    Join Date
    Apr 2007
    Posts
    2

    Field Missing

    It appears that it's the [Incident Report No] field which is giving you a hassle. It's the sole column that appears to not allow nulls and is not in your insert statement.

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    eDIT

    Code:
    INSERT INTO dbo.Report
       (  [Incident Report No],
          Area, 
          LossType, 
          Loss
       )
       SELECT
          dbo.[Main Table].[IR Number]
          dbo.[Main Table].Area, 
          dbo.[Main Table].LossType, 
          dbo.[Main Table].Loss
       FROM dbo.[Main Table]
       WHERE [Incident Report No] = [IR NUMBER]
    
    RETURN

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •