What are the challenges you face when working across database platforms? Take the survey
Options

INSERT expansion should not add certain fields

mscheunermscheuner Posts: 57
edited February 26, 2010 3:33AM in SQL Prompt Previous Versions
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 :)

Comments

  • Options
    Anu DAnu D Posts: 876 Silver 3
    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?
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • Options
    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
            &#40; DocFileName ,
              DocContent ,
              DocLength
            &#41;
    VALUES  &#40; '' , -- DocFileName - varchar&#40;255&#41;
              NULL , -- DocContent - image
              0  -- DocLength - int
            &#41;
    

    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
  • Options
    Anu DAnu D Posts: 876 Silver 3
    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.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • Options
    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
  • Options
    Anu DAnu D Posts: 876 Silver 3
    Good!

    Kindly let us know if you have any other queries, I'll like to help!
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
Sign In or Register to comment.