Inserted, Deleted Logical table in SQL Server

ere are Inserted and Deleted logical tables in SQL Server. These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert,Update,Delete) on a database table.

Use of logical tables

Basically, logical tables are used by triggers for the following purpose:

1. To test data manipulation errors and take suitable actions based on the errors.
2. To find the difference between the state of a table before and after the data modification and take actions based on that difference.

Inserted logical Table

The Inserted table holds the recently inserted or updated values means new data values. Hence newly added and updated records are inserted into the Inserted table.

Suppose we have Employee table as shown in fig. Now We need to create two triggers to see data with in logical tables Inserted and Deleted.

 CREATE TRIGGER trg_Emp_Ins
ON Employee
FOR INSERT
AS
begin
SELECT * FROM INSERTED -- show data in Inserted logical table
SELECT * FROM DELETED -- show data in Deleted logical table
end 

Now insert a new record in Employee table to see data with in Inserted logical table.

 INSERT INTO Employee(EmpID, Name, Salary) VALUES(3,'Avin',23000)
SELECT * FROM Employee 

Deleted logical Table

The Deleted table holds the recently deleted or updated values means old data values. Hence old updated and deleted records are inserted into the Deleted table.

 CREATE TRIGGER trg_Emp_Upd
ON Employee
FOR UPDATE
AS
begin
SELECT * FROM INSERTED -- show data in INSERTED logical table
SELECT * FROM DELETED -- show data in DELETED logical table
 end 

 --Now update the record in Employee table to see data with in Inserted and Deleted logical tables
Update Employee set Salary=43000 where EmpID=3
SELECT * FROM Employee 

We could not create the logical tables or modify the data with in the logical tables. Except triggers, When you use the OUTPUT clause in your query, logical tables are automatically created and managed by SQL Server. OUTPUT clause also has access to Inserted and Deleted logical tables just like triggers.

Get nth highest and lowest salary of an employee

Examples:

employee name and salary as shown in below fig.

Query to get nth(3rd) Highest Salary

 Select TOP 1 Salary as '3rd Highest Salary' 
from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC) 
a ORDER BY Salary ASC 

Query to get nth(3rd) Lowest Salary

 Select TOP 1 Salary as '3rd Lowest Salary' 
from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary ASC) 
a ORDER BY Salary DESC 

Happy  Coding!!!!!!

Control Flow Tasks # – Difference between Sequence ,For Each Loop and For Loop Container Task

Why to use this task ?

The container and looping tasks allow us to repetitively run a set of tasks for a set number of times or for each element in a collection, such as all files in a folder.

We are taking about the following three task lets look :

Use of Sequence Container Task

• When we want perform some task sequentially or parallel we are going to make use of most of sequence container
• which means though the name of the task is sequence container make point it can perform following two task:(A) Sequential execution of task (B)Parallel execution of task
• The Sequence container groups together several tasks.
• Use it to define a transaction boundary around a set of tasks so they all fail or succeed together. Or, use it simply to reduce the clutter on the design surface by hiding the detailed steps within the sequence.
• We can also group control flow objects, and collapse or expand those groups. There’s no task for grouping.
Use of For Each Loop Container Task

• For Each Loop container is falls under container and looping tasks
• Use containers like the For Each Loop and For Loop to execute a set of tasks multiple times.
• For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics.
• In short when we have to iteratively execute set of task we will insert all those task under For Loop Container and set the values accordingly
• The for each loop container acts as a repeating control flow in a package. Its operations are similar to work of For each keyword in any advanced programming language. We have a definite type of enumerator for each type of objects.
• Loop implementation in the For Each Loop Container is similar to the Foreach looping concept in various programming languages.
Use of For Loop Container Task

• Name of the task itself explains most of of it !! For Loop container is falls under container and looping tasks
• Use containers like the For Each Loop and For Loop to execute a set of tasks multiple times.
• For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics. 
  In short when we have to iteratively execute set of task we will insert all those task under For Loop Container and set the values accordingly
• For loop task is the looping implementation of a task and also This task will evaluate an expression and loops through the process and until the evaluation goes to False.

Comparison between this task ?

• Following are key point which will describe difference between above task which falls under same category
• Use For loop task when you know exact how many time we have repeat given Data flow for execution
• Use For Each Loop when there is dependency on no of time task should execute on data such as files,variable we will use this.
• And when we know exact sequence and sometime we need to perform parallel operation based on our requirement.
• With a For Each loop, we have a pre-determined number of times we will execute the loop. this could be rows in a table, files in a folder, or items in a list.
• In a For Each loop we can specify what the list is by manipulating the properties and loop enumerator.
• In a for loop we will execute the tasks a specified number of times, in other words n times, or 25 times, and the number of times is specified in the definition of the container
• A for each loop will execute once for each item in the collection of items that it is looking at.

How to load the un structured flat files into SQL Server in SSIS

Input file:
"PartNumber"|"PSSBaseUoM"|"PSSBusinessNbr"|"PSSComponentType"|"PSSDetailedDesc"|
"0000"|"P"|""|""|"FOURNITURE DE :"
"0000411420"|"P"|""|"Finished Goods"|"ROND"ELLE DENTELEE"
"0000510040"|"P"|""|"Component"|"INSERT"
"0001"|"P"|""|""|"MATERIEL INFO"RMATIQUE"
"0002"|"P"|""|""|"MAINTENANCE"
"0003"|"P"|""|""|"MOBILIER CONSOMMABLE"
"0004"|"P"|""|""|"OUTILLAGE B.E."
"0005"|"P"|""|""|"CONTROLE"
"002008815"|"P"|""|"Component"|"BOITE "STOCKAGE"
"0044490070"|"P"|""|"Finished Goods"|"COUVERCLE"
"0064137000"|"P"|""|"Finished Goods"|"FLASQUE COMPLET"
"0064140210"|"P"|""|"Component"|"FLAS"QUE"

Output:

PartNumber	PSSBaseUoM	PSSBusinessNbr	PSSComponentType	PSSDetailedDesc
0	P			FOURNITURE DE :
411420	P		Finished Goods	RONDELLE DENTELEE
510040	P		Component	INSERT
1	P			MATERIEL INFORMATIQUE
2	P			MAINTENANCE
3	P			MOBILIER CONSOMMABLE
4	P			OUTILLAGE B.E.
5	P			CONTROLE
2008815	P		Component	BOITE STOCKAGE
44490070	P		Finished Goods	COUVERCLE
64137000	P		Finished Goods	FLASQUE COMPLET
64140210	P		Component	FLASQUE

Solution:

Use the script component as a source and write below C# code to perform the above output.

// C# code

using System;

using System.Data;

using System.IO;    // Added

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    public override void CreateNewOutputRows()

    {

        int i = 0;

        // Read file (use the FILE connection added in the script component, named myFile)

        using (StreamReader sr = new StreamReader(this.Connections.Connection.ConnectionString, System.Text.Encoding.UTF7)) // Encoding is optional

        {

            String line;

            // Read lines from the file until the end of the file is reached.

            while ((line = sr.ReadLine()) != null)

            {

                if (i != 0)

                {

                    // Split the line into columns

                    string[] columns = line.Split('|');

                    // Add one new row

                    this.Output0Buffer.AddRow();

                    // Fill columns, but check if they exist

                    if (columns.Length > 0)

                    {

                        // Remove the " at the start and end of the string

                        // with a trim or use a substring.

                        //Output0Buffer.PartNumber = columns[0].TrimStart('"').TrimEnd('"');

                        Output0Buffer.PartNumber = columns[0].Contains("\"") ? columns[0].Replace("\"", "") : columns[0];

                    }

                    if (columns.Length > 1)

                    {

                        //Output0Buffer.PSSBaseUoM = columns[1].TrimStart('"').TrimEnd('"');

                        Output0Buffer.PSSBaseUoM = columns[1].Contains("\"") ? columns[1].Replace("\"", "") : columns[1];

                    }

                    if (columns.Length > 2)

                    {

                        //Output0Buffer.PSSBusinessNbr = columns[2].TrimStart('"').TrimEnd('"');

                        Output0Buffer.PSSBusinessNbr = columns[2].Contains("\"") ? columns[2].Replace("\"", "") : columns[2];

                    }

                    if (columns.Length > 3)

                    {

                        //Output0Buffer.PSSComponentType = columns[3].TrimStart('"').TrimEnd('"');

                        Output0Buffer.PSSComponentType = columns[3].Contains("\"") ? columns[3].Replace("\"", "") : columns[3];

                    }

                    if (columns.Length > 4)

                    {

                        // Output0Buffer.PSSDetailedDesc = columns[4].TrimStart('"').TrimEnd('"');

                        Output0Buffer.PSSDetailedDesc = columns[4].Contains("\"") ? columns[4].Replace("\"", "") : columns[4];

                    }

                }

                i++;

            }

        }

    }

}

Finally load the data into your destination.

DAX Queries on SSRS report design with ALL parameter implementation

All parameter implementation: At runtime itself, the report selects “All” which selects all the columns he wants to select to display on the report.

Advantage: When the report selects “All” it couldn’t hit the where condition in the report.it improve the query & report performance at run time.

1.    Building Tabular Model

In Microsoft Visual Studio, Go to File menu >> New >> Project.

Select Business Intelligence Analysis Services & Analysis Services Tabular Project. Provide the project name & location. 

It then asks for the workspace server details.

The project gets created. It has a model (bim file) which stores all the details of the tabular project.

Create Data Source Connection. Here, we use SQLserver connection.

We directly import five tables from Data source. The model gets created as-

Save the project. Build & Deploy it on the required server.

After building a simple Tabular Model, we’ll build report on it & use various reporting features.

2.    Building Report & Various Features

In Microsoft Visual Studio, Go to File menu >> New >> Project.

Select Business Intelligence Reporting Services & Report Server Project. Provide the project name & location. 

The project gets created. In solution Explorer, Add a new report.

We’ll make a simple Report.

Create an Analysis services Data source connection for report, pointing to the Tabular Model created.

Then, create a dataset for the data source.

We will use DAX expressions & Query Designer &use the design mode to command type DMX.

In the Field section of Dataset Properties, we can see the list of all columns pertaining to the table referred. We can rename the field names as per our need.

In the Design View, Create a simple table enlisting all columns in the desired order.

The report gets created.

       Filter Parameters & “All”  Feature

To filter records-

·         Category

Add parameter Category for user selection.

Set Default Values as “All”. To run the report automatically by selecting “All” while running the report.

At runtime it comes as “All” which selects all the data at runtime.

Dataset’s Creation:

We need to create 2 dataset’s for this report.

1.    DS_Main:

DAX:

Evaluate

(

   Summarize

   (

      Calculatetable

      (

        'Internet Sales',

(PATHCONTAINS (@Category,'Product Category'[Product Category Name]) || @Category=" All" ) ),

         'Product Category'[Product Category Name],

         'Product Subcategory'[Product Subcategory Name],

        'Product'[Product Name],

         'Date'[Calendar Year],

         "Total Sales Amount", sum('Internet Sales'[Sales Amount])

   )

)

Order by 'Product Category'[Product Category Name] ASC

We have to pass the pathcontains with OR (||) condition to select “ All” by default.

Note: Provide the space for “ All” to display on top the data.

2.    DS_Fltr_Category

We’ll create a separate dataset DS_Fltr_Category to populate list of values for Category parameter using table ‘product Category’.

DAX:   

Evaluate

(

 Summarize

  (

    (

     AddColumns (

                  (

                                         Calculatetable

                                         (

                                                Summarize

                                                (

                                                'Product Category',

                                                Rollup ('Product Category'[Product Category Name])

                                                 

                                                )

                                         )

                                  ),

   "Category", If (ISBlank ([Product Category Name]),  " All",[Product Category Name] )

                 )

                ),

                [Category]

   )

)

ORDER BY [Category] ASC

It will list all the Categories in a sorted order.

We’ll add a new parameter ‘Category’ and allow multiple values. Allow multiple values option enables user to select more than values from the available list.

It will take all available values from dataset ds_fltr_category values.

 

Give the space on value  “ All” to get the All on top of the data.

The Parameter is defined and JOIN expression is given to handle multiple values of Category selected by user.

By default “All” selects by during runtime of the report to display.