insert into table variable with identity column

Provides intelligent code completion for SQL Server editors.

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Paul Stephenson, Michelle Taylor, Mike Upton, justin.caldicott, Aaron Law

insert into table variable with identity column

Postby duhnbr » Wed Jul 18, 2012 5:20 pm

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:
Code: Select all
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
duhnbr
 
Posts: 10
Joined: Fri Mar 10, 2006 3:42 pm
Location: Earth

Postby eddie davis » Thu Jul 19, 2012 8:09 pm

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
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Postby PDinCA » Thu Jul 19, 2012 11:02 pm

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?
PDinCA
 
Posts: 517
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA

Postby eddie davis » Wed Jul 25, 2012 4:34 pm

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
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Postby PDinCA » Wed Jul 25, 2012 5:49 pm

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
PDinCA
 
Posts: 517
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA


Return to SQL Prompt 5

Who is online

Users browsing this forum: No registered users and 0 guests