| Author |
Message |
duhnbr
Joined: 10 Mar 2006
Posts: 8
Location: Earth
|
 Posted: Wed Jul 18, 2012 5:20 pm Post subject: insert into table variable with identity column |
 |
|
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 |
|
 |
eddie davis
Joined: 14 Jun 2006
Posts: 796
Location: Red Gate Software
|
| Posted: Thu Jul 19, 2012 8:09 pm Post subject: |
|
|
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 |
|
|
PDinCA
Joined: 25 Jul 2005
Posts: 464
Location: Costa Mesa, CA, USA
|
| Posted: Thu Jul 19, 2012 11:02 pm Post subject: |
|
|
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 |
|
|
eddie davis
Joined: 14 Jun 2006
Posts: 796
Location: Red Gate Software
|
| Posted: Wed Jul 25, 2012 4:34 pm Post subject: |
|
|
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 |
|
|
PDinCA
Joined: 25 Jul 2005
Posts: 464
Location: Costa Mesa, CA, USA
|
| Posted: Wed Jul 25, 2012 5:49 pm Post subject: |
|
|
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 |
|
|
|
