insert into table variable with identity column

MCTSQLMCTSQL Posts: 14 Bronze 3
edited July 25, 2012 12:49PM in SQL Prompt Previous Versions
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(id int identity(1,1), value int);

--statement generated by SQL Prompt
insert into @t([id],[value]) values()

--statement that SHOULD be generated by SQL Prompt
insert into @t([value]) values()
MCTSQL

Comments

  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    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
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • PDinCAPDinCA Posts: 642 Silver 1
    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?
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    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
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • PDinCAPDinCA Posts: 642 Silver 1
    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
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
Sign In or Register to comment.