Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Question Unanswered: Msg 8101, Level 16, State 1, Line 68

    I'm getting this error in SQL server.
    Msg 8101, Level 16, State 1, Line 68
    An explicit value for the identity column in table 'temp_notes' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    My original script looked like this.

    SET NOCOUNT ON

    DECLARE @patient_id int
    ,@phn varchar(9)
    ,@full_name varchar(100)
    ,@birth_date datetime
    ,@entry_note varchar(8000)
    ,@entry_date datetime
    ,@rec_counter int
    ,@Clinic_identifier varchar(24)
    ,@chart_count int

    set @Clinic_identifier = 'MEDOWL.'
    set @rec_counter = 0


    DECLARE patients_cursor CURSOR FOR
    SELECT a.full_name, a.birth_date, a.phn, a.patient_id
    FROM patient as a
    ,clinicdoctors as b
    WHERE a.patient_id IN (51450,49741,57290) --= 51450
    and datalength(a.phn) = 9
    and b.clinicdoctor_id = a.clinicdoctor_id
    ORDER BY a.last_name,a.first_name,a.patient_id

    OPEN patients_cursor
    FETCH NEXT FROM patients_cursor
    INTO @full_name, @birth_date, @phn, @patient_id

    --select @full_name, @birth_date, @phn, @patient_id


    WHILE @@FETCH_STATUS = 0

    BEGIN

    DECLARE chartcount_cursor CURSOR FOR
    select count(*)
    from ChartEntries
    where patient_id = @patient_id


    OPEN chartcount_cursor
    FETCH NEXT FROM chartcount_cursor INTO @chart_count

    --select @chart_count as chtcount

    DECLARE chartentries_cursor CURSOR FOR
    select entry_datetime,entry_note
    from ChartEntries
    where patient_id = @patient_id
    order by Patient_id, ChartEntry_Id desc

    -- Variable value from the outer cursor

    OPEN chartentries_cursor
    FETCH NEXT FROM chartentries_cursor INTO @entry_date,@entry_note
    --select @entry_date,@entry_note

    WHILE @@FETCH_STATUS = 0

    BEGIN
    set @rec_counter = @rec_counter + 1
    --select @@FETCH_STATUS as status
    IF @@FETCH_STATUS = 0

    insert into temp_notes
    values (@Clinic_identifier+@phn+'.'
    +convert(varchar,@rec_counter)+'.'
    +convert(varchar,@chart_count),
    @phn,
    @full_name,
    @birth_date,
    @entry_date,
    @entry_note
    )

    -- Get the next entry note
    FETCH NEXT FROM chartentries_cursor INTO @entry_date,@entry_note
    --select @entry_date,@entry_note

    END
    CLOSE chartentries_cursor
    DEALLOCATE chartentries_cursor
    CLOSE chartcount_cursor
    DEALLOCATE chartcount_cursor


    set @rec_counter = 0
    set @chart_count = 0

    -- Get the next patient record
    FETCH NEXT FROM patients_cursor
    INTO @full_name, @birth_date, @phn, @patient_id

    --select @full_name, @birth_date, @phn, @patient_id

    -- Get the next chart record count
    --FETCH NEXT FROM chartcount_cursor INTO @chart_count
    --select @chart_count as chtcount

    END

    CLOSE patients_cursor
    DEALLOCATE patients_cursor

    GO

    I then changed it to left pad the variables @rec_counter and @chart_count.
    SET NOCOUNT ON

    DECLARE @patient_id int
    ,@phn varchar(9)
    ,@full_name varchar(100)
    ,@birth_date datetime
    ,@entry_note varchar(8000)
    ,@entry_date datetime
    ,@rec_counter int
    ,@Clinic_identifier varchar(24)
    ,@chart_count int

    set @Clinic_identifier = 'MEDOWL.'
    set @rec_counter = 0


    DECLARE patients_cursor CURSOR FOR
    SELECT a.full_name, a.birth_date, a.phn, a.patient_id
    FROM patient as a
    ,clinicdoctors as b
    WHERE a.patient_id IN (51450,49741,57290) --= 51450
    and datalength(a.phn) = 9
    and b.clinicdoctor_id = a.clinicdoctor_id
    ORDER BY a.last_name,a.first_name,a.patient_id

    OPEN patients_cursor
    FETCH NEXT FROM patients_cursor
    INTO @full_name, @birth_date, @phn, @patient_id

    --select @full_name, @birth_date, @phn, @patient_id


    WHILE @@FETCH_STATUS = 0

    BEGIN

    DECLARE chartcount_cursor CURSOR FOR
    select count(*)
    from ChartEntries
    where patient_id = @patient_id


    OPEN chartcount_cursor
    FETCH NEXT FROM chartcount_cursor INTO @chart_count

    --select @chart_count as chtcount

    DECLARE chartentries_cursor CURSOR FOR
    select entry_datetime,entry_note
    from ChartEntries
    where patient_id = @patient_id
    order by Patient_id, ChartEntry_Id desc

    -- Variable value from the outer cursor

    OPEN chartentries_cursor
    FETCH NEXT FROM chartentries_cursor INTO @entry_date,@entry_note
    --select @entry_date,@entry_note

    WHILE @@FETCH_STATUS = 0

    BEGIN
    set @rec_counter = @rec_counter + 1
    --select @@FETCH_STATUS as status
    IF @@FETCH_STATUS = 0

    insert into temp_notes
    values (@Clinic_identifier+@phn+'.'
    +REPLICATE('0',3),convert(varchar,@rec_counter)+'. '
    +REPLICATE('0',3),convert(varchar,@chart_count),

    @phn,
    @full_name,
    @birth_date,
    @entry_date,
    @entry_note
    )

    -- Get the next entry note
    FETCH NEXT FROM chartentries_cursor INTO @entry_date,@entry_note
    --select @entry_date,@entry_note

    END
    CLOSE chartentries_cursor
    DEALLOCATE chartentries_cursor
    CLOSE chartcount_cursor
    DEALLOCATE chartcount_cursor


    set @rec_counter = 0
    set @chart_count = 0

    -- Get the next patient record
    FETCH NEXT FROM patients_cursor
    INTO @full_name, @birth_date, @phn, @patient_id

    --select @full_name, @birth_date, @phn, @patient_id

    -- Get the next chart record count
    --FETCH NEXT FROM chartcount_cursor INTO @chart_count
    --select @chart_count as chtcount

    END

    CLOSE patients_cursor
    DEALLOCATE patients_cursor

    GO

    That is when I received the error, so I inserted this command before the insert line but it made no difference.

    SET IDENTITY_INSERT temp_notes ON
    insert into temp_notes
    values (@Clinic_identifier+@phn+'.'
    +REPLICATE('0',3),convert(varchar,@rec_counter)+'. '
    +REPLICATE('0',3),convert(varchar,@chart_count),
    @phn,
    @full_name,
    @birth_date,
    @entry_date,
    @entry_note
    )

    I'm concatenating four different variables together to insert into the column note_id.

    What is the problem? I just don't see it. My table definition looks like this.

    CREATE TABLE [dbo].[temp_notes](
    [temp_Id] [int] IDENTITY(1,1) NOT NULL,
    [note_Id] [varchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [phn] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [full_name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [birth_date] [datetime] NULL,
    [entry_datetime] [datetime] NULL,
    [Entry_Note] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CONSTRAINT [PK_temp_notes] PRIMARY KEY CLUSTERED
    (
    [temp_Id] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You are trying to insert into your identity column, but you don't realise it!
    Code:
    insert into temp_notes
    values (@Clinic_identifier+@phn+'.'
    +REPLICATE('0',3),convert(varchar,@rec_counter)+'. '
    +REPLICATE('0',3),convert(varchar,@chart_count),
    @phn,
    @full_name,
    @birth_date,
    @entry_date,
    @entry_note
    )
    You are not explicitly declaring which columns you're inserting into!
    Your construct should look like this
    Code:
    INSERT INTO <table name> (<field name 1>, <field name 2>, ... , <field name N>)
    VALUES (<value 1>, <value 2>, ... , <value N>)
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2008
    Posts
    2

    Thanks georgev

    Thanks for the reply, but I don't believe that is the problem. The insert worked just fine before I added the REPLICATE statements. If you insert the data values in the order that the columns are defined you don't need to list the column names. My first column in the table, the PK, is an automatic incrementing sequence number and so I couldn't put a value in for that anyway. The error has definately got something to do with the REPLICATE that I added, but I don't know what.

    Wayne

Posting Permissions

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