Red Gate forums :: View topic - insert into table variable with identity column
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 5
SQL Prompt 5 forum

insert into table variable with identity column

Search in SQL Prompt 5 forum
Post new topic   Reply to topic
Jump to:  
Author Message
duhnbr



Joined: 10 Mar 2006
Posts: 10
Location: Earth

PostPosted: Wed Jul 18, 2012 5:20 pm    Post subject: insert into table variable with identity column Reply with quote

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:
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()

_________________
SQL PROCtologist
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 889
Location: Red Gate Software

PostPosted: Thu Jul 19, 2012 8:09 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
PDinCA



Joined: 25 Jul 2005
Posts: 499
Location: Costa Mesa, CA, USA

PostPosted: Thu Jul 19, 2012 11:02 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 889
Location: Red Gate Software

PostPosted: Wed Jul 25, 2012 4:34 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
PDinCA



Joined: 25 Jul 2005
Posts: 499
Location: Costa Mesa, CA, USA

PostPosted: Wed Jul 25, 2012 5:49 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group