This article has nothing really to do with any eOne products or even Microsoft Dynamics for that matter. It’s about a feature in Microsoft SQL that not many seem to know about. I must admit that I was unaware of this until a week or so ago, and I found it so incredibly useful now that I began sharing it with others, only to find out it was news to them as well.
So, in the spirt of Tech Tuesday, I am happy to share with you, a very simple method to drag and drop table columns directly into SQL statements
If you write a lot of large SQL queries against tables, you know that it’s best to to avoid manually typing if you can. People are prone to misspell something and while it’s seems easy enough to just type out “select * from MYTABLE”, especially if you only need 2 or 3 fields from the table, the larger the query, the larger the chance for error. For instance, if you are doing a select statement on a table with 130 fields and you need to get 100 of them typing out the names of 100 fields, is both time-consuming and susceptible to mistakes.
What I have done in the past is type “select * from MYTABLE”, then deleted the asterisk and used IntelliSense to finish the query. So you start typing in the name of your field, then select it from the dropdown, and then start typing the name of your next field and select that from the drop down. It works, I mean, I’ve been doing it like that for years, but its not the easiest way.
If you go into SQL Management studio there is an easy way to get all the fields from a table into a SQL query window. You need to expand the table and highlight the columns folder. Then if you click and drag the column into a new query window, it will put all the field names for the entire table for you. It’s that simple!
In the following screenshot, you can see I start to type a select statement. I want to select all the fields from my SOP10200 table, but there are 130 of them. I just type in “SELECT” and then I highlight the columns folder and drag it into my query.
After dragging and dropping, my query window looks like this:
You still have to complete the query and put “from SOP10200” at the end of it, however it’s a lot faster than using IntelliSense to manually add the fields one by one.
Where I find this incredibly useful is writing insert statements. If you are writing an insert and not populating every field in the table, you have to manually specify the field names you are doing the inserts to. If you use the drag and drop method you can just drag the columns into your query.
So in this screen, I have typed “INSERT INTO SOP10200 (“
Now I drag and drop my columns into the query and get this result.
You can right click on the table and select “Script table as – Insert To – New Query Editor Window” and get the same result, however SQL always formats the Insert statement down the page which I find more difficult to read. I would rather have all the fields across the page so that I can add my values in the next line without having to scroll up and down to determine what field I am inserting into.
You can also drag individual fields into a query. So if I am starting to type a SQL select statement, I can actually start the statement and then drop the fields I want into the query. So in this case I typed in “select from SOP10200” into my query window. Then I highlight the fields one by one and drag them into my query. You will need to manually type the comma between each field. This is useful for tables where you don’t know what the field name start with. In cases like that, using IntelliSense doesn’t really work because you have to start typing the name in order for it to appear in the field selection drop down.
So I hope this little tip helps you in building your SQL statements. It’s not anything special, but it seems to be quite helpful. Give it a shot. Share it with someone you know, and who knows, you just might make their day.