SQL Server 2012
using Database "AdventureWorks2012"
Table "DatabaseLog"
This is from the Message screen in SSMS:
Add column names to temp table as data:
INSERT INTO ##DatabaseLog
( [DatabaseLogID],[PostTime],[DatabaseUser],
[Event],[Schema],[Object],[TSQL],[XmlEvent],[Order] )
VALUES
( 'DatabaseLogID','PostTime','DatabaseUser','Event',
'Schema','Object','TSQL','XmlEvent', '1' )
------------------------------------------
Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table
'##DatabaseLog' when IDENTITY_INSERT is set to OFF.
Column "[DatabaseLogID]" is type INT
Identity Specification is "YES"
If I add:
SET IDENTITY_INSERT dbo.DatabaseLog ON
Just above
EXEC(@SQL)
Which is approx. 5 lines below
PRINT 'Add column names to temp table as data:'
I get the message below:
------------------------------------------
Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table '##DatabaseLog' when IDENTITY_INSERT is set to OFF.
A Table with a Column that contains an Identity Specification will NOT work with this Procedure.
The code that assembles the Fields needs to check if the Field is an "identity column" and use NULL as the value.
Marc Crane