locked
Unable to do INSERT on table RRS feed

  • Question

  • Hi,

    I have a table that won't allow me to do an INSERT. When I run an INSERT statement I get the following error:

    Msg 8101, Level 16, State 1, Line 2
    An explicit value for the identity column in table 'twork' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    I have admin writes.

    Can anybody please show me how to get around this?

    Cheers

    Paul

    Friday, August 16, 2013 12:48 PM

Answers

  • to let sql server auto-increment the value:

    insert into twork values('0', '0', '292', '2013-08-14', '1000', '0', '25', '29', NULL, '1', NULL, '0', '0', NULL)
    

    to put the id in yourself:

    set identity_insert twork on
    insert into twork ([work_id],[user_id],[require_update],[employee_id],[date_and_time],[type],[status],[daily_schedule_id],[period_schedule_id],[period_schedule_pay_period_id],[contribute],[memo],[override_rate_minute],[override_rate_cost],[override_anomaly])
    values('983743', '0', '0', '292', '2013-08-14', '1000', '0', '25', '29', NULL, '1', NULL, '0', '0', NULL)



    Thanks, Andrew

    • Marked as answer by runnerpaul Tuesday, August 20, 2013 9:31 AM
    Friday, August 16, 2013 1:13 PM

All replies

  • Hi Paul, if you show the insert statement and table DDL, it will be easier to show the exact problem.  However, this error is a result of trying to assign a value to an identity column.  You will likely want to just omit this column from your insert statement and let SQL Server assign the value.

    Thanks,
    Sam Lester (MSFT)


    http://blogs.msdn.com/b/samlester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, August 16, 2013 12:52 PM
  • Hi,

    Either

    1) Don't specify the identity column when inserting (SQL Server will increment the value in that field itself for every row you insert)

    2) set IDENTITY_INSERT on and do it yourself



    Thanks, Andrew

    • Proposed as answer by Sergio S Arias Friday, August 16, 2013 1:23 PM
    Friday, August 16, 2013 12:52 PM
  • Thanks for the quick responses.

    Here's my SQL:

    insert into twork values('983743', '0', '0', '292', '2013-08-14', '1000', '0', '25', '29', NULL, '1', NULL, '0', '0', NULL)

    Here's the table DDL:

    USE [timeware_main_6]
    GO
    /****** Object:  Table [dbo].[twork]    Script Date: 08/16/2013 14:03:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[twork](
    	[work_id] [int] IDENTITY(1,1) NOT NULL,
    	[user_id] [int] NULL CONSTRAINT [DF__twork__user_id__13F1F5EB]  DEFAULT ((0)),
    	[require_update] [bit] NOT NULL CONSTRAINT [DF__twork__require_u__14E61A24]  DEFAULT ((0)),
    	[employee_id] [int] NOT NULL CONSTRAINT [DF__twork__employee___15DA3E5D]  DEFAULT ((0)),
    	[date_and_time] [datetime] NOT NULL,
    	[type] [int] NOT NULL CONSTRAINT [DF__twork__type__16CE6296]  DEFAULT ((0)),
    	[status] [int] NULL,
    	[daily_schedule_id] [int] NULL,
    	[period_schedule_id] [int] NULL,
    	[period_schedule_pay_period_id] [int] NULL,
    	[contribute] [bit] NOT NULL CONSTRAINT [DF__twork__contribut__17C286CF]  DEFAULT ((0)),
    	[memo] [ntext] NULL,
    	[override_rate_minute] [bit] NOT NULL CONSTRAINT [DF__twork__override___18B6AB08]  DEFAULT ((0)),
    	[override_rate_cost] [bit] NOT NULL CONSTRAINT [DF__twork__override___19AACF41]  DEFAULT ((0)),
    	[override_anomaly] [bit] NULL,
     CONSTRAINT [aaaaatwork_PK] PRIMARY KEY NONCLUSTERED 
    (
    	[work_id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[twork]  WITH CHECK ADD  CONSTRAINT [twork_FK00] FOREIGN KEY([daily_schedule_id])
    REFERENCES [dbo].[tdaily_schedule] ([daily_schedule_id])
    GO
    ALTER TABLE [dbo].[twork] CHECK CONSTRAINT [twork_FK00]
    GO
    ALTER TABLE [dbo].[twork]  WITH CHECK ADD  CONSTRAINT [twork_FK01] FOREIGN KEY([employee_id])
    REFERENCES [dbo].[temployee] ([employee_id])
    ON DELETE CASCADE
    GO
    ALTER TABLE [dbo].[twork] CHECK CONSTRAINT [twork_FK01]
    GO
    ALTER TABLE [dbo].[twork]  WITH CHECK ADD  CONSTRAINT [twork_FK02] FOREIGN KEY([period_schedule_id])
    REFERENCES [dbo].[tperiod_schedule] ([period_schedule_id])
    GO
    ALTER TABLE [dbo].[twork] CHECK CONSTRAINT [twork_FK02]

    Friday, August 16, 2013 1:05 PM
  • Oh. By the way I tried switching IDENTITY_INSERT on but still no joy.
    Friday, August 16, 2013 1:07 PM
  • Here are examples of the two options in code:

    CREATE TABLE [dbo].[twork](
    	[work_id] [int] IDENTITY(1,1) NOT NULL,
    	[user_id] [int] NULL ,
    	[require_update] [bit] NOT NULL )
    
    -- An explicit value for the identity column in table 'twork' can only be specified when a column list is used and IDENTITY_INSERT is ON.
    insert into twork values (77, 7, 0)
    
    -- Cannot insert explicit value for identity column in table 'twork' when IDENTITY_INSERT is set to OFF.
    insert into twork (work_id, user_id, require_update)
    values (77, 7, 0)
    
    -- Option 1 -- Omit the identity column in your update statement and let SQL Server set it
    insert into twork (user_id, require_update)
    values (7, 0)
    
    -- Option 2 -- set IDENTITY_INSERT off
    SET IDENTITY_INSERT dbo.twork ON
    GO
    insert into twork (work_id, user_id, require_update)
    values (77, 7, 0)
    
    Thanks,
    Sam Lester (MSFT)

    http://blogs.msdn.com/b/samlester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Sergio S Arias Friday, August 16, 2013 1:23 PM
    Friday, August 16, 2013 1:12 PM
  • to let sql server auto-increment the value:

    insert into twork values('0', '0', '292', '2013-08-14', '1000', '0', '25', '29', NULL, '1', NULL, '0', '0', NULL)
    

    to put the id in yourself:

    set identity_insert twork on
    insert into twork ([work_id],[user_id],[require_update],[employee_id],[date_and_time],[type],[status],[daily_schedule_id],[period_schedule_id],[period_schedule_pay_period_id],[contribute],[memo],[override_rate_minute],[override_rate_cost],[override_anomaly])
    values('983743', '0', '0', '292', '2013-08-14', '1000', '0', '25', '29', NULL, '1', NULL, '0', '0', NULL)



    Thanks, Andrew

    • Marked as answer by runnerpaul Tuesday, August 20, 2013 9:31 AM
    Friday, August 16, 2013 1:13 PM
  • When using  IDENTITY_INSERT, you need to include the column list as mentioned in the error message.

    Thanks,
    Sam Lester (MSFT)


    http://blogs.msdn.com/b/samlester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, August 16, 2013 1:15 PM
  • Your real problem is the design of this table. You have more NULL-able columns than entire payroll systems. You use assembly language BIT flags instead of SQL (at least you know that BIT is NULL-able). Most of the column names are too vague to be valid much less conforming to ISO-11179 standards? Why do you think that “type” (blood?, job?), “status” (Marriage? Employment?) are precise and clear? NO, it is vague and generic! 

    Worst of all, you think that IDENTITY can be used as a key. What does the physical insertion attempts count to a disk has to do with a logical data model? How do you validate or verify it? 

    What kind of entity is a “Twork”? Putting a meta data affix in a data element name is a design error so bad it has a name.. Tblling, from the “tbl_” that noobs use. This comes from the first compilers for FORTRAN II and tape operating systems. They needed to have physical meta data in the names to locate files and hardware. Tape drives would begin with “MT-”, integers in FORTRAN began with the letters I to N. BASIC had to use $ in a string name. Etc. 

    Why are you using integer for employee identifiers? Do you do math with them? The NTEXT, TEXT and IMAGE data types will be removed in a future version of Microsoft SQL Server. 

    What are the Daily_Schedules and Period_Schedules? Why do you not have a “Something_Schedules” which has values for the day and the periods? This sounds like a two-column primary key, but we cannot tell. 

    What you have is a 1950's magnetic tape file system with assembly language programs that depend on bit flags. You just happen to use SQL. Your use of IDENTITY table property (it is not a column!) is how non-RDBMS programmers fake the record number on a mag tape. SQL programmers use keys; keys are subsets of non-NULLable columns that uniquely locate a row in the table. 

    Here is a start at a re-write, just to get your started. You ned to re-write4 and quit getting kludges on forums to keep this mess running.  

    CREATE TABLE Work_Somethings
    (employee_id INTEGER NOT NULL
       REFERENCES Personnel(employee_id),
     something_timestamp DATE NOT NULL,
     daily_schedule_id INTEGER NOT NULL
       REFERENCES Daily_Schedules (daily_schedule_id),
     period_schedule_id INTEGER NOT NULL 
       REFERENCES Period_Schedules (period_schedule_id),
     PRIMARY KEY (?????), 
     period_schedule_pay_period_id INTEGER NOT NULL,
     blood_type CHAR(2) NOT NULL,
     marriage_status INTEGER NOT NULL,
     something_memo VARCHAR(200),

    --start of assembly language flags, remove them! 
     require_update_flg BIT NOT NULL,
     contribute_flg BIT NOT NULL,
     override_rate_minute_flg BIT NOT NULL,
     override_rate_cost_flg BIT NOT NULL,
     override_anomaly_flg BIT NOT NULL);

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, August 16, 2013 3:49 PM