0

There are many posts about getting the error:

"Explicit value must be specified for identity column in table" "either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column"

The problem is always that the user is trying to do something like:

insert into table1 select * from table2

In my case, I AM supplying column names, but still getting that error.

EDIT: The reason I'm trying to capture the identity column is because the temporary table is a test version of when I will ultimately insert other data into RC_Incoming. My algorithm is:

  1. Create TempTable as a clone of RC_Incoming
  2. Insert data from some other table into TempTable (letting RecordID be auto generated)
  3. Make sure everything looks right
  4. If ok, then do it for realz -- insert in to RC_Incoming.

Code:

<cfquery datasource="#LOCAL.DataSource#">
    IF OBJECT_ID('tempdb.dbo.##TempTable') IS NOT NULL
        DROP TABLE ##TempTable
    ELSE
    DBCC CHECKIDENT ([##TempTable], RESEED, 1);
    CREATE TABLE ##TempTable
    (   
        [RecordID] [INTEGER] IDENTITY NOT NULL,
        [StudentID] [varchar](15) NOT NULL,
        [CourseNumber] [varchar](6) NOT NULL,
        [CompDateTime] [datetime] NOT NULL,
        [SystemInfo] [varchar](250) NULL,
        [WriteTime] [datetime] default GETDATE() NULL,
        PRIMARY KEY CLUSTERED 
        (
            [StudentID], [CourseNumber], [CompDateTime]
        )
        WITH
        (
            PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
        )
    )
</cfquery>


<cfquery datasource="#LOCAL.DataSource#">
    SET IDENTITY_INSERT ##TempTable ON
    INSERT INTO ##TempTable
        (
            RecordID
            ,StudentID
            ,CourseNumber
            ,CompDateTime
            ,SystemInfo
            ,WriteTime
        )
    SELECT  RecordID
            ,StudentID
            ,CourseNumber
            ,CompDateTime
            ,SystemInfo
            ,WriteTime
     FROM   RC_Incoming
</cfquery>

I'm still getting:

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Explicit value must be specified for identity column in table '#TempTable_______________________________________________________________________________________________00000000066E' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

RC_Incoming already has data in it, and has the exact same structure (done in SQL Server Management Studio):

CREATE TABLE dbo.[RC_Incoming]
(
    [RecordID] [INTEGER] IDENTITY NOT NULL,
    [StudentID] [varchar](15) NOT NULL,
    [CourseNumber] [varchar](6) NOT NULL,
    [CompDateTime] [datetime] NOT NULL,
    [SystemInfo] [varchar](250) NULL,
    [WriteTime] [datetime] default GETDATE() NULL,
    PRIMARY KEY CLUSTERED 
    (
        [StudentID], [CourseNumber], [CompDateTime]
    )
    WITH
    (
        PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
    ) ON [PRIMARY]
) ON [PRIMARY]
abalter
  • 9,663
  • 17
  • 90
  • 145
  • If recordID is an IDENTITY column, leave it out. Don't `SELECT` it, don't `INSERT` it. – Bernhard Döbler Oct 21 '15 at 20:49
  • Hm.. that raises a good question. What are you ultimately doing with the inserted RecordID values? – Leigh Oct 21 '15 at 20:51
  • *(letting RecordID be auto generated)* Not sure I follow. You are not letting it be auto generated. SET IDENTITY_INSERT ... ON does the opposite - ie Lets you insert the id's manually instead of having SQL Server generate them automatically. – Leigh Oct 21 '15 at 22:15
  • When I append the table `RC_Incoming` by inserting the new data (operation not shown), the new id's will be auto generated, presumably consecutively from the current max key. That's fine, and will all happen later. The FIRST order of business is to be able to create the temporary table `TempTable` for testing that is an EXACT clone of the original table `RC_Incoming`. Presumably, when I insert the new data into `TempTable` it will go in exactly as when I insert into `RC_Incoming` for real. – abalter Oct 21 '15 at 22:22
  • The reason for asking "why" (ie what is the end goal) is because it did not sound like you actually need an identity column. However, if the purpose is to test out a new process that will ultimately occur on the "real" table, that would be a reason to use an IDENTITY column here. Anyway, getting back to the code. First, it contains several different spellings for the temp table. Is that a typo or code error? – Leigh Oct 21 '15 at 22:38
  • Yup you got it. I unified the spelling. It should be `TempTable`. – abalter Oct 21 '15 at 22:54
  • The code above works fine (after fixing the table names). However, it sounds like you are trying to insert more records into #TempTable somewhere - *without* the recordID column. That would cause an error because you forgot to turn the setting off again at the end of the query: `SET IDENTITY_INSERT ##TempTable OFF`. Be very careful with db settings. Some are applied for the life of the db session. Since CF uses connection pooling by default, a single db connection may be reused by multiple requests, making it very easy to inadvertently impact other requests if you forget to reset something.. – Leigh Oct 22 '15 at 02:24
  • Should also be noted that this approach is susceptible to concurrency issues. I don't know your application, but if you have multiple users, they could hit this process simultaneously, and you could end up with duplicate identity records, or unexpected data in your temp table. – beloitdavisja Oct 22 '15 at 02:31
  • (Edit) @beloitdavisja - Non-global temp tables are per "session". As long as you drop them at the end of the request (before the connection is returned to the pool), it should be okay. Concurrency is only issue if the process involves permanent tables, which *are* accessible across sessions. That said, this type of sql is really better suited to a stored proc. It is a lot easier to control setting changes, and avoid inadvertent leakage, if the logic is wrapped in a procedure, instead of being spread across multiple queries. – Leigh Oct 22 '15 at 03:14
  • @Leigh - Correct, the temp table isn't much of a worry, but the permanent table is what I would be most concerned with. Seems like maybe a different approach should be implemented. I'm still unclear why the ID is important to have in the temp table. Are the IDs being used somewhere else in the process? – beloitdavisja Oct 22 '15 at 12:32
  • The ID is important in the temp table because it exists in the real table `RC_Incoming`. I want to make sure that whatever `Record_ID` values the rows have in `RC_Incoming`, they have in the temp table. That way, hopefully, inserting the new data into either table will be exactly the same. – abalter Oct 22 '15 at 14:34
  • (Edit) @abalter - We understand the CF and SQL code, but you have not clearly explained the use case here. What is your ultimate goal in plain English (not the code you think you need to accomplish the task)? For example, "I am trying to populate TableB with dummy data from ...", "I am trying to duplicate X records...", OR "I am trying to regenerate the record id's in TableA so they are contiguous". Those examples are probably way off base, but would give us an idea of what this process is ultimately trying to accomplish. – Leigh Oct 22 '15 at 16:06
  • Here it is in explicit detail. Note, when the offending code is not commented out, I get the error: `IDENTITY_INSERT is already ON for table 'tempdb.dbo.#TempTable__________________________________________________________________________________________________________000000007433'. Cannot perform SET operation for table '#UltimateTargetTable'.` http://data.stackexchange.com/stackoverflow/query/379206/testing-insert-by-cloning-into-temp-table – abalter Oct 22 '15 at 18:08
  • Is this thing on? ;-) That is not what I asked. *Why* are you trying to do this? I already explained the cause of the original error above - and how to fix it. However, session related issues like this are common due to CF's connection pooling. That is why this kind of logic generally belongs in a procedure. Since you have not explained the goal, or why you cannot use a procedure as suggested, not sure what else to say. – Leigh Oct 25 '15 at 03:34

0 Answers0