CODEWITHSUNDEEP
sql-servert-sql
goofyui
goofyui

Reputation: 3492

How to have an identity column for a temp table in SQL?

How to have an identity column for a temp table in SQL?

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

I am getting SQL Syntax error for the below block.

 if object_id('tempdb.dbo.#t') is not null  
   drop table #t

create table #t
(
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [TotalCount] int,
  [PercentageComplete] nvarchar(4000)
)

insert into #t
  select totalcount, percentagecomplete from table_a

Upvotes: 1

Views: 40190

Answers (1)

Saharsh
Saharsh

Reputation: 760

Add this to your query after table declaration

SET IDENTITY_INSERT #t OFF

This should fix it. The following code works on my machine

CREATE TABLE #t
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [TotalCount] INT,
    [PercentageComplete] NVARCHAR(4000)
)

SET IDENTITY_INSERT #t OFF 

INSERT INTO #t
    SELECT
        totalcount, percentagecomplete 
    FROM
        table_a

Upvotes: 6

Related Questions