Step types: Query

Hi again,

I want to tell you about package step types in 4 different posts. The current step types are Object, Query, List and Export. In this blog post, I want to talk about "Query" step type.

But before that, please let me remind you about the basics of package and step concept. Packages consist of steps and steps are T-SQL DML commands like INSERT, UPDATE, DELETE or DDL commands like DROP PROCEDURE, ALTER VIEW, CREATE TABLE etc.

DML commands and DROP DDL commands are entered to the package as Query steps. CREATE and ALTER DDL commands are entered as Object types. If you want to transfer your data from your one environment to another then you choose the Export step type. List is a type, but not a step. If you need to execute some SELECT statements against your source or target servers then you choose the List type from the type list and perform your actions accordingly. This will help you when you are writing your DML codes in the Query step type window.

Now let's talk about the Query step type. To get a better understanding about the Query types please take a look at the following screen shot.

Step details window which shows details of a particular Query step type.

The image above belongs to a Query step's details. Using the "Get row count" button in the right lower corner of the window, you can learn exactly how many records will be affected by this statement at that moment. The SELECT command in the Query result section will be created dynamically by TransSQL using the original UPDATE command you enter. You will see a button with a header of "Run" just at the right side of the SELECT statement, the button executes this SELECT statement against the target database and returns the row count to the right of the button itself.

You will see an edit box about which is labeled "Row count to be affected". You can edit this box and enter a row count which is supposed to be affected. This function is useful especially when you are not sure of the affected row count. If the statement is an UPDATE or DELETE statement and if the affected row count exceeds the row count you enter to this box, then the statement will be rolled back when executed by the administrator. So you will prevent from making a mistake.

You are not obliged to enter only one line of code to a Query step. You can enter many statements at once and parser will parse all those DELETE and UPDATE commands and create SELECT statements accordingly. With this in mind, it will be more practical to create different Query steps for each statement.

You will also see a button which is captioned "Comment out" in this window. You can use this button to comment out all the commands at once. This function is there just to make things practical. You will want to use this button for instance when the Database Admin calls you and say "Hey X, I altered a Stored Procedure you wrote because of some performance problems. Here's the code I altered, please enter it to TransSQL so that it will be in our records".

You can also update your note in this window. With this note, you can transmit some useful messages to the administrator which can help him/her while he/she is executing the package.

In this blog post I tried to tell you about the Query step type and some of its functions. I hope you will like it as we do!

Thanks,
Ekrem Onsoy