CODEWITHSUNDEEP
sql-serversql-server-2008
HAJJAJ
HAJJAJ

Reputation: 3777

An explicit value for the identity column?

I am trying to make a backup for my data from my first table DocumentManagement.tbDocumentsHistory and save it on DocumentManagement.tbDocuments

and because the DocumentManagement.tbDocuments have ID IDENTITY_INSERT column this error will raised

An explicit value for the identity column in table 'DocumentManagement.tbDocuments' can only be specified when a column list is used and IDENTITY_INSERT is ON.

when i used this code 

DELETE FROM DocumentManagement.tbDocumentsHistory
OUTPUT DELETED.* INTO DocumentManagement.tbDocuments 
FROM    DocumentManagement.tbDocumentsHistory
WHERE   DocumentID=@DocumentID

I tried to turn off the IDENTITY_INSERT on DocumentManagement.tbDocuments using this code 

SET IDENTITY_INSERT DocumentManagement.tbDocuments ON 
        DELETE FROM DocumentManagement.tbDocumentsHistory
            OUTPUT DELETED.* INTO DocumentManagement.tbDocuments 
            FROM    DocumentManagement.tbDocumentsHistory
        WHERE   DocumentID=@DocumentID
        SET IDENTITY_INSERT DocumentManagement.tbDocuments OFF

and I am still facing the same error!

my tables desc :

CREATE TABLE [DocumentManagement].[tbDocuments](
[DocumentID] [bigint] IDENTITY(1,1) NOT NULL,
[DocumentNameEn] [nvarchar](max) NULL,
[DocumentNameAr] [nvarchar](max) NULL,
[DocumentDescriptionEn] [nvarchar](max) NULL,
[DocumentDescriptionAr] [nvarchar](max) NULL,
[CreatedOn] [datetime2](7) NULL,
[ModifiedOn] [datetime2](7) NULL,
[AddedBy] [bigint] NULL,
[modifaiedBy] [bigint] NULL,
[PhysicalName] [nvarchar](max) NULL,
[Extension] [nvarchar](15) NULL,
[DocumentTypeID] [bigint] NULL,
[PhysicalPath] [nvarchar](max) NULL,


CONSTRAINT [PK_tbDocuments] PRIMARY KEY CLUSTERED([DocumentID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [DocumentManagement].[tbDocumentsHistory](
[DocumentID] [bigint] NOT NULL,
[DocumentNameEn] [nvarchar](max) NULL,
[DocumentNameAr] [nvarchar](max) NULL,
[DocumentDescriptionEn] [nvarchar](max) NULL,
[DocumentDescriptionAr] [nvarchar](max) NULL,
[CreatedOn] [datetime2](7) NULL,
[ModifiedOn] [datetime2](7) NULL,
[AddedBy] [bigint] NULL,
[modifaiedBy] [bigint] NULL,
[PhysicalName] [nvarchar](max) NULL,
[Extension] [nvarchar](15) NULL,
[DocumentTypeID] [bigint] NULL,
[PhysicalPath] [nvarchar](max) NULL, CONSTRAINT [PK_tbDocumentsHistory] PRIMARY KEY CLUSTERED([DocumentID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

Upvotes: 3

Views: 8250

Answers (3)

Monticola Explorator
Monticola Explorator

Reputation: 1318

TL;DR: The column list specification that the error is requesting is the column list of the destination table. In this case, OUTPUT DELETED.* INTO DestinationTable (IdentityColumn, othercolumn, anothercolumn,...), it won't work if you just explicitly indicate the columns from the source data instead of doing Deleted.*.

The error message states that you have to turn on identity insert and specify a column list. The problem lies in the fact that you have not specified a column list for your insert.

You can check out the output clause documentation to see how you indicate a column list there. In this case, taking into account the columns on your own accepted answer, your sql code would look something like:

DELETE FROM DocumentManagement.tbDocumentsHistory
OUTPUT DELETED.DocumentID, DELETED.DocumentNameEn, Deleted.DocumentNameAr,
Deleted.DocumentDescriptionEn, Deleted.DocumentDescriptionAr, Deleted.CreatedOn, 
Deleted.ModifiedOn, Deleted.AddedBy, Deleted.modifaiedBy, Deleted.PhysicalName, 
Deleted.Extension, Deleted.DocumentTypeID, Deleted.PhysicalPath  INTO DocumentManagement.tbDocuments
( 
                  DocumentID, DocumentNameEn , DocumentNameAr , DocumentDescriptionEn ,
                  DocumentDescriptionAr , CreatedOn , ModifiedOn ,
                  AddedBy , modifaiedBy , PhysicalName ,Extension ,
                  DocumentTypeID ,PhysicalPath
)
FROM    DocumentManagement.tbDocumentsHistory
WHERE   DocumentID=@DocumentID

Upvotes: 0

HAJJAJ
HAJJAJ

Reputation: 3777

this will solve the problem but I was trying to make it better using the OUTPUT

SET IDENTITY_INSERT DocumentManagement.tbDocuments ON
        INSERT INTO DocumentManagement.tbDocuments
                ( DocumentID, DocumentNameEn , DocumentNameAr , DocumentDescriptionEn ,
                  DocumentDescriptionAr , CreatedOn , ModifiedOn ,
                  AddedBy , modifaiedBy , PhysicalName ,Extension ,
                  DocumentTypeID ,PhysicalPath
                )   
        SELECT * FROM  DocumentManagement.tbDocumentsHistory
        SET IDENTITY_INSERT DocumentManagement.tbDocuments OFF

        DELETE FROM DocumentManagement.tbDocumentsHistory           
        FROM    DocumentManagement.tbDocumentsHistory
        WHERE   DocumentID=@DocumentID

Upvotes: 3

Mithrandir
Mithrandir

Reputation: 25337

You need to modify your INSERT-statement and add a column list containing the name of the identity column for this to work.

Any INSERT-statement using an identity insert needs a columns list, like in this:

CREATE TABLE dbo.Tab 
(
   ID INT IDENTITY NOT NULL PRIMARY KEY, 
   Name VARCHAR(40) NOT NULL
)

SET IDENTITY_INSERT dbo.Tab ON
GO

INSERT INTO dbo.Tab (ID, Name) VALUES (3000, 'Groucho')
GO

SET IDENTITY_INSERT dbo.Tab OFF

Upvotes: 3

Related Questions