SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
INSERT INTO and UPDATE Auto Populate
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
INSERT INTO and UPDATE Auto Populate
Author Message
isken.r



Joined: 16 Mar 2012
Posts: 16
Country: Australia

Post INSERT INTO and UPDATE Auto Populate Reply with quote
Hi guys,

A quick question. Why is it when I type in INSERT INTO (or UPDATE) and then select a table from the list using the arrow keys without typing any letters of the table and hit Enter, SQL Assistant automatically generates the whole INSERT INTO or UPDATE statement for all columns. But when I type in INSERT INTO (or UPDATE) then type in first few letters to locate the table I'm after then hit enter, it just auto completes the table name without generating the INSERT INTO or UPDATE statement? Is this by design or am I missing something? I love this feature because it saves a lot of time, but our DB is quite big with lots of tables, so locating a table using just the arrow keys without typing a few starting letters of a table might take a long time.

Please let me know if there is a workaround.

Thanks
Wed Mar 21, 2012 9:01 pm View user's profile Send private message
lfromzel



Joined: 21 Mar 2012
Posts: 2
Country: United States

Post Reply with quote
First, when you type a few letters, the selection in popup gets filtered, so locating the correct table is not that difficult even if you have large database.

Second, as soon as you typed enough letters to make the selection unique ( only one table name will be shown in popup), you should get the complete statement including that table name. If it does not work like that for you, please open SA, go to options and check that Auto-Select First List Item is set to 'yes'.
Wed Mar 21, 2012 9:58 pm View user's profile Send private message
isken.r



Joined: 16 Mar 2012
Posts: 16
Country: Australia

Post Reply with quote
I don't think you understood my question. I have nothing against the way we can locate the tables using SQL Assistant, that works as expected. The problem that I have is with the auto-completion feature of the INSERT INTO and UPDATE commands. It works inconsistently. See below...

Scenario 1:
1. Type in INSERT INTO (or UPDATE)
2. When a list of tables is presented use ONLY the keyboard arrow keys to locate the table you want. IMPORTANT: Do not type any letters of the table, locate it using ONLY the keyboard keys.
3. Hit Enter
4. A full INSERT INTO or UPDATE script is generated for all columns of the selected table, like:
INSERT INTO dbo.table1
(
col1,
col2,
col3
)
VALUES
(
/*{ col1 }*/,
/*{ col2 }*/
/*{ col3 }*/
)

Scenario 2:
1. Type in INSERT INTO (or UPDATE)
2. Type in first few letters of the table you're after
3. Locate the table and hit Enter
4. The table name is auto-completed for you

Do you see the difference? The full INSERT INTO or UPDATE scripts are ONLY generated when you haven't typed in any letters of the table yet, so if you have a huge DB with lots of tables, the only way to auto-generate the INSERT INTO or UPDATE script is to locate the table using the keyboard arrow keys, otherwise it just auto-completes the table name.

My question is, how can I auto-generate the full INSERT INTO or UPDATE script for all columns of the table I'm after, if I've already typed in a few letters of the table and located it that way?

Hope this makes sense.

Thanks
Wed Mar 21, 2012 11:52 pm View user's profile Send private message
lfromzel



Joined: 21 Mar 2012
Posts: 2
Country: United States

Post Reply with quote
I understood your question.

Which version of SA you are using? What database and what editor?

I tested it with SA 6.0.86 and SQL Management Studio; scenario #2 works exactly as scenario #1, I had a full INSERT generated, with a correct table name; same thing if I type enough letters to have only one matching table name in the popup and then press "Enter" without selecting anything in the pop up.

If you tell me your version and editor, I'll check if the default behavior in our older versions was different, and if it can be modified through options.
Thu Mar 22, 2012 12:15 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Hi there, as far as I know the behavior in both cases 1 and 2 is the same in *ALL*versions. Which editor are you using? Are you expanding table levels using Arrow-Right key before pressing enter? Are you holding down Shift key when pressing Enter?
Thu Mar 22, 2012 12:45 am View user's profile Send private message
isken.r



Joined: 16 Mar 2012
Posts: 16
Country: Australia

Post Reply with quote
Thanks for your quick reply.

We are using the latest SQL Assistant v6.1 on both SQL Server 2008 and 2012.

I see what you mean... It does indeed work as expected but ONLY when not using a schema name. We have a lot of tables on custom schema, so we are forced to type in a schema name along with the Table names.

Can you please try reproducing the same steps but after typing in a schema name (even if it is a default [dbo])? The you will definitely see what I mean.

Thank you
Kind regards
Thu Mar 22, 2012 12:48 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I guess the problem is that you expand the schema level by hitting Enter and pressing '.' to expand the schema level instead of using the right arrow key as stated by SysOp above. I that case scenario #2 works as you described because you get a different kind of popup to select the table from. Using the right arrow key it works as scenario #1.

By the way, is VALUES used that frequently it has to be auto-inserted? I have to delete it most of the times. I tried to substitute it with a snippet using $COLUMNS$ but that one does not auto comment identity fields. Is there a way to force INSERT INTO not to insert the VALUES part?
Thu Mar 22, 2012 3:18 am View user's profile Send private message
isken.r



Joined: 16 Mar 2012
Posts: 16
Country: Australia

Post Reply with quote
To be honest, I think the behavior should be consistent regardless of whether I typed in a custom schema or not, or how I typed it in, or how I selected the table name from the list, it should be smart enough to figure it out and generate the INSERT or UPDATE script. I can't find a way to generate the INSERT or UPDATE scripts for the table I'm after any other way but to use the keyboard arrow keys and that's very annoying.

We definitely don't use the INSERT and UPDATE script auto generate feature on a daily basis but often enough to need it, and considering we have some huge tables with lots of columns, it can be a real pain to script them manually.
Thu Mar 22, 2012 5:30 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Hi, I believe it is consistent. I think you've missed our point. It provides you with options allowing you to choose what code and how you want to insert it, the end result depends on what you choose (options). Have I missed anything?
Thu Mar 22, 2012 5:51 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Well, that's something we disagree on then. I would not be happy if it were smart enough and created the whole INSERT part (just as the VALUES part) each and every time. It came in handy several times that I was able to break the auto-generation sequence by using the 'enter' to select the schema and '.' to re-initialize the popup. Having SA insert some 60 columns when I only have to insert values into 3 or 4 of them is just as annoying. Which is actually the case with 6.2 preview, it is too smart when it comes to INSERT INTO. SysOp, could you check that, please?

Regarding you not finding the way how to auto-generate INSERT/UPDATE, I regularly update and insert into tables that are in arbitrary schemas. And yes, I enter them by typing their schema first, using right-arrow key (only once) and then typing the tablename, finishing with an 'enter', so you are definitely doing something wrong.
Thu Mar 22, 2012 5:56 pm View user's profile Send private message
isken.r



Joined: 16 Mar 2012
Posts: 16
Country: Australia

Post Reply with quote
Hi. Thanks for your quick response. You might be right and I might be missing something in the options, that's exactly why I raised this thread. The behavior must be consistent regardless of how you find your table from the list, right?? I don't want to repeat it all over again, but please try the following two scenarios step by step. I might not be clear enough but I think you're missing my point.

IMPORTANT: Please follow the below steps EXACTLY as described.

Scenario 1 (INSERT or UPDATE are automatically generated)
1. Type in INSERT INTO
2. Type in dbo
3. Type in . (dot)
4. Using only the keyboard arrow keys choose any table from the list
5. Hit Enter
6. Voila! INSERT INTO script is generated, right?

Scenario 2 (INSERT or UPDATE are NOT automatically generated)
1. Type in INSERT INTO
2. Type in dbo
3. Type in . (dot)
4. Type in the first few letters of the target table
5. Chose the table from the list using the keyboard arrow keys UNLESS it is already auto-selected for you in the list
5. Hit Enter
6. Only the table name is auto-completed for you. THERE IS NO AUTO_GENERATED INSERT SCRIPT.

I hope you see what I mean now? Our DB is huge and tables are all over different schema, so we MUST use Scenario 2, because locating a table using the arrow keys alone without typing in a few first letters to quick-locate the tables will take ages.

So, how do we get the INSERT INTO and UPDATE scripts to generate automatically for the scenario 2?

P.S. There is no question as to whether this feature is useful or not, the question is why it is inconsistent or how to get it to do what we expect it to do?

Thanks
Thu Mar 22, 2012 6:15 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Okay, let me get this straight :) I don't have to follow your description step-by-step because (as I already told you) I'm aware it works this way. Please, read back and find my post where I mentioned using the right arrow key. I did not mean the arrow key that is proper but the one that points to your right. I also mentioned that the problem is that you're probably using '.' to select the table. Typing '.' and letters after the schema gives you another type of popup (I told that as well). You have to expand the schema using 'the arrow key that points to the right'. So what you should do is the following.

1. Type in INSERT INTO
2. Type in you schema (or just the first few letters of it until it is selected).
3. Forget about typing '.' (dot). Just forget it. You type 'right arrow key'. Your schema gets expanded. If it has already been expanded (I guess it depends on some settings, but I'm not sure) it folds back, so you have to press it again (yes, this one here is inconsistent)
4. Type in the first few letters of the target table until you narrow it down to the one you need (you can use arrow keys, but you don't have to).
5. Chose the table from the list using the keyboard arrow keys UNLESS it is already auto-selected for you in the list
5. Hit Enter
6. Voila! INSERT INTO script is generated, right?


EDIT: meanwhile I've figured out that the schema gets expanded as soon as it is narrowed down to one. While it can be arguably useful because you can see the first few tables in the schema, it makes you have to press right-arrow key twice to expand and insert into the editor so that you can go on with typing the table name. My personal opinion is that it should not do that (or at least have an option to turn this behavior off).
Thu Mar 22, 2012 6:26 pm View user's profile Send private message
isken.r



Joined: 16 Mar 2012
Posts: 16
Country: Australia

Post Reply with quote
Wow! :))) Now, that's not very intuitive, is it?? What the hell?? Using the Right Arrow Key to expand a schema is just something we haven't even thought about, to be honest...

Thanks heaps for your answer, it works now.

Having said that though, it still seems like it is more of a workaround than the proper behavior. I would even dare to say that it is a BUG.

It is very weird that the auto-generation of scripts works as expected even when you type in a . (dot) after a schema but then use only the arrow keys, but somehow it doesn't work when you type in at least one letter. That's just plain weird and I think it should be fixed to keep it consistent.
Thu Mar 22, 2012 7:07 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Maybe. But maybe they just think that way. For some people, using the right arrow key may not be the first idea, for others, it might. For me it was not and I got my answers from SysOp a couple of months (years?) ago. But it's mentioned dozens of times in the help for selecting and expanding objects, hence it's not a workaround, it's intentional. Just not a mainstream approach :) While I thought it to be funny at first, I got used to it quite easily and quickly and I don't find it weird anymore.
Thu Mar 22, 2012 7:28 pm View user's profile Send private message
isken.r



Joined: 16 Mar 2012
Posts: 16
Country: Australia

Post Reply with quote
Cool! Thanks again for your answer. I guess we just have to live with it or maybe try SQL Prompt as well and see how that pans out...

PS. Even though the solution might already be described somewhere in help, it still doesn't deny the fact that the behavior is inconsistent. To keep it consistent, in an INSERT or UPDATE statement after typing in the .(dot) after the schema name, SA has to either:
- Always auto-complete the table name
or
- Always auto-generate the full script

But not both behaviors depending on whether a user has typed in at least one character to locate the table or exclusively used keyboard arrow keys to locate the table. And considering the behavior when no schema name is specified (SA ALWAYS generates the full INSERT or UPDATE script when no schema name is specified), it should continue doing so when a schema name is specified.
Thu Mar 22, 2012 7:40 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to: 
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
Design by Freestyle XL / Flowers Online.