CODEWITHSUNDEEP
sql-servervariablesprimary-keyidentity
Jackson
Jackson

Reputation: 1

Table Variable with Primary and Identity Key

I'm trying to enter a table variable with a primary key and identity key but I keep getting error "Msg 8101, Level 16, State 1, Line 313 An explicit value for the identity column in table '@Table4' can only be specified when a column list is used and IDENTITY_INSERT is ON."

Example of Code: DECLARE @Table4 TABLE (ProductID INT PRIMARY KEY IDENTITY (1,1) ,DocumentNode VARCHAR(50) ,ModifiedDate DATE)

INSERT INTO @Table4 SELECT ProductID, DocumentNode, ModifiedDate FROM [Production].[ProductDocument]

SELECT * FROM @Table4

Upvotes: 0

Views: 633

Answers (1)

Jim Berg
Jim Berg

Reputation: 659

Your problem is that you are specifying that ProductID is an identity column and then trying to insert a ProductID value. Remove IDENTITY(1,1) for it to work.

Upvotes: 0

Related Questions