INSERT expansion should not add certain fields

mscheuner

Folks,

I really enjoy the SQL Prompt INSERT statement expansion - very useful.

HOWEVER: for two types of fields, I think those should be omitted from the list of fields (IDENTITY fields are already being filtered out, so you must be checking field properties already, to a degree):

* computed fields are - by their very nature - computed and thus read-only - you will never be able to insert a specific value into a computed field

* TIMESTAMP (ROWVERSION) fields are special cases in SQL Server that a user INSERT statement can never insert a value into - so why do they show up in the list of fields in the expanded INSERT statement?? All I can do is remove them from that list.... and you could do that, too, right when expanding the INSERT statement

Anu D

Many thanks for your post.

Yes you are correct about the timestamp and I will make a note of it.

But SQL Prompt doesn't add the computed columns in 'insert into' statements.

Can you kindly email us the script for your table where SQL Prompt is adding computed column in list?

mscheuner

Anu Deshpande wrote:

But SQL Prompt doesn't add the computed columns in 'insert into' statements.

Ah - yes it does ! (but it shouldn't)


Try this:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DocumentStore](
	[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, 
	[DocFileName] [varchar](255) NULL,
	[DocContent] [image] NULL,
	[DocLength]  AS (datalength([DocContent])) PERSISTED,
)
GO

SET ANSI_PADDING OFF
GO

When I go ahead and type

INSERT INTO dbo.DocumentStore

and how I press <ENTER> on my keyboard, this gets expanded into:

INSERT INTO dbo.DocumentStore
        ( DocFileName ,
          DocContent ,
          DocLength
        )
VALUES  ( '' , -- DocFileName - varchar(255)
          NULL , -- DocContent - image
          0  -- DocLength - int
        )

and quite clearly, the "DocLength" column is a computed column and there's really absolutely no point for me to insert a value into that column!

SQL Prompt 4.0.3.10 on SQL Server 2008

Anu D

Ah ha I see what is happening!

Can you kindly update your version to 4.0.4.11?

Link to update is here: http://www.red-gate.com/messageboard/vi ... hp?t=10248

Kindly let me know whether it fixes the issue or not.

mscheuner

Anu Deshpande wrote:

Ah ha I see what is happening!
Can you kindly update your version to 4.0.4.11?
Kindly let me know whether it fixes the issue or not.

Yes, that definitely solves that problem - thanks!

Marc

Anu D

Good!

Kindly let us know if you have any other queries, I'll like to help!