How to find column in value list easily in Management Studio?

I've been trying to find some way to simplify editing/adding a value to the corresponding column inside Insert-clauses when coding inside SQL inside Managagement Studio 2008 R2.

Assume the following code:

INSERT INTO [Schema].[Table]
                ( [Col1],
                  [Col2],
                  [Col3],
                  [Col4],
                  [Col5],
                  [Col6],
                  [Col7],
                  [Col8],
                  [Col9],
                  [Col10],
                  [Col11],
                  [Col12],
                  [Col13],
                  [Col14],
                  [Col15],
                  [Col16],
                  [Col17],
                  [Col18]

                )
         VALUES
                (
                  232,
                  0,
                  NULL,
                  NULL,
                  0,
                  NULL,
                  NULL,
                  NULL,
                  NULL,
                  NULL,
                  0,
                  NULL,
                  NULL,
                  NULL,
                  NULL,
                  NULL,
                  NULL,
                  NULL

Say I want to change a value in the 11th column, I have to start counting to see which value corresponds to that column. Perhaps it's not that difficult in this example but many real life tables contains a lot of columns. This becomes more difficult if there are a lot of NULLS in the value list. Even with line numbers enabled.

Is there a way to have, for example a tooltip at mouse over on the value in the value list to see which column it belongs to?

In my dream world I would right click on the column name in the Insert Into and choose something like "Go to value...".

I'm using the free ApexSQL tools but I haven't found a function that enables me to do it. Are there any similar tools that allows me to do this?

Thanks in advance and hope I'm not too ambigious in what I'm trying to achieve.

Answers


I'm not aware of any facility to do this, but when I've run into this issue, I usually just add comments to the code, something like:

    VALUES
                (
                  232,  --Col1
                  0,    --Col2
                  NULL, --Col3
                  NULL, --Col4
                  --etc.
                )

I haven't seen an option like this.

Although it requires more typing, I prefer SELECT over VALUES because I can alias the inputs, saving time later. Also SELECT statements can be previewed (by highlighting just that section and pressing F5 - very handy).

Alternative you can turn on line numbers in SSMS and calculate the correct row. Actually scrap that. Doesn't sound very user friendly does it?

INSERT INTO Schema.Table
    (
        Col1,
        Col2,
        Col3,
        Col4,
        Col5
    )
SELECT
    1       AS Col1,
    0       AS Col2,
    NULL    AS Col3,
    1       AS Col4,
    NULL    AS Col5
;

This might be easier if you restructured the query to use insert . . . select:

INSERT INTO [Schema].[Table]([Col1], [Col2], [Col3], [Col4], [Col5], [Col6],
                             [Col7], [Col8], [Col9], [Col10], [Col11], [Col12],
                             [Col13], [Col14], [Col15], [Col16], [Col17], [Col18]
                            )
    SELECT 232 as col1,
           0 as col2,
           NULL as col3,
           NULL as col4,
           0 as col5,
           NULL as col6,
           . . .;

You can use block selection and INSERT SELECT for one value insert:

INSERT INTO [Schema].[Table]( 
     [Col1]
    ,[Col2]
    ,[Col3]
    ,[Col4]
    ,[Col5]
    ,[Col6]
    ,[Col7]
    ,[Col8]
    ,[Col9]
    ,[Col10]
    ,[Col11]
    ,[Col12]
    ,[Col13]
    ,[Col14]
    ,[Col15]
    ,[Col16]
    ,[Col17]
    ,[Col18]
)
SELECT
     [Col1]    = 232
    ,[Col2]    = 0 
    ,[Col3]    = NULL
    ,[Col4]    = NULL
    ,[Col5]    = 0
    ,[Col6]    = NULL
    ,[Col7]    = NULL
    ,[Col8]    = NULL
    ,[Col9]    = NULL
    ,[Col10]   = NULL
    ,[Col11]   = NULL
    ,[Col12]   = 0
    ,[Col13]   = NULL
    ,[Col14]   = NULL
    ,[Col15]   = NULL
    ,[Col16]   = NULL
    ,[Col17]   = NULL
    ,[Col18]   = NULL

The key is to format columns like ,[Column_Name] then you use block selection to copy them all. After that another block selection and type multiple = NULL at once. At the end fill desired values.

Example of block selection from: https://www.mssqltips.com/tipimages2/2786_ssms_shift_alt_select.png


Need Your Help

In Java when does a URL connection close?

java exception url connection

When does java let go of a connections to a URL? I don't see a close() method on either URL or URLConnection so does it free up the connection as soon as the request finishes? I'm mainly asking to ...