Exclude Identity Field From Auto Generated Insert/Values Code For Table Variables
If a table variable or temp table has an identity field, I do not think this field should show up in the auto generated insert into/values list:
DECLARE @Table TABLE (TableId INT IDENTITY(1,1), Data VARCHAR(100);
INSERT INTO @Table
( [TableId] -- This field show not show up by default in the insert list
, [Data] )
VALUES ( 0 -- TableId - int -> This field show not show up by default in the insert values list
, '' -- Data - varchar(100)
)
-- Temp Table
CREATE TABLE #Temp (TableId INT IDENTITY(1,1), Data1);
INSERT INTO [#Temp] ([TableId],
[Data1]) VALUES (0 -- TableId - int
, -- Data1 -
)
![](https://secure.gravatar.com/avatar/99b49741e1b245891da1369577deca62?size=40&default=https%3A%2F%2Fassets.uvcdn.com%2Fpkg%2Fadmin%2Ficons%2Fuser_70-6bcf9e08938533adb9bac95c3e487cb2a6d4a32f890ca6fdc82e3072e0ea0368.png)
This is included in SQL Prompt 7.1 which you can download from http://www.red-gate.com/products/sql-development/sql-prompt/
-
JBrune commented
I think this doesn't go under the Tab history forum
-
Stephen commented
As a DEFAULT behavior this is "normal", but when there are times one uses the SET IDENTITY_INSERT ON/OFF pair, having the IDENTITY column in the Insert list is beneficial.
Perhaps RG should give us the option...