insert into table variable with identity column

MCTSQL · July 18, 2012 12:20PM

When generating an insert statement for a table variable containing an identity column, SQL Prompt 5.3.2.2 incorrectly includes the identity column into the inserted columns list.

Example:

declare @t table&#40;id int identity&#40;1,1&#41;, value int&#41;;

--statement generated by SQL Prompt
insert into @t&#40;&#91;id&#93;,&#91;value&#93;&#41; values&#40;&#41;

--statement that SHOULD be generated by SQL Prompt
insert into @t&#40;&#91;value&#93;&#41; values&#40;&#41;

Eddie D · July 19, 2012 3:09PM

Hi, thank you for your post into the forum.

It looks like a bug to me as I was able to reproduce it. I have submitted Bug Report SP-4474 to bring the error to my collegeagues in the SQL Prompt Development Team. I will be maintaining what our Bug Tracking system calls a 'Watch' on the Bug Report submitted. This means I can update this forum topic on any changes that occur to the report.

Many Thanks
Eddie

PDinCA · July 19, 2012 6:02PM

It would actually be beneficial if we had the option to generate the insert WITH the IDENTITY column, and have the whole statement top-n-tailed by a pair of SET IDENTITY_INSERT <Table> ON/OFF statements.

I have several reference tables that include an IDENTITY PK and must explicitly set the IDENTITY when replicating manually from Production to all other servers, as transaction replication is way overkill for this.

Any chance consideration could be given to enabling the above option (dialog, perhaps) upon detection of an INSERT INTO ... fragment?

Eddie D · July 25, 2012 11:34AM

Hi PDinCA

I have submitted a feature request, reference SP-4479, for SQL Prompt to support or have an option to INSERT WITH IDENTITY.

I cannot guarantee the success of the feature request submitted or if approval is given what future version of the product it will appear in.

I recommend also that you post an entry on the SQL Prompt UserVoice forum requesting this feature, available through this LINK. As the more votes your suggestion receives, will increase its chances of making it into a future version of SQL Prompt.

Many Thanks for your suggestion.

Eddie Davis

PDinCA · July 25, 2012 12:49PM

Thanks, Eddie, at least it's in the pile... Can't hurt to ask.

I've exhausted my voting capacity on Uservoice, or I'd gladly add another... The votes I've already spent are for what I consider important, too, so there's no scope for redistribution, sadly.

Cheers,

Stephen

insert into table variable with identity column

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk