SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

ELT() vs FILED() Functions

MySQL supports some functions that can be used to extract the index position or the actual value from a set of values arranged as an array. ELT() and FIELD() are example of such functions.

ELT() function accepts many arguments where the first argument is the number. It returns the value which is available at the position specified in the first argument.

1
SELECT ELT(3,'SQL','ORACLE','My SQL','SQL SERVER') AS ColumnName;

MySQL - ELT() and FILED() Functions to Extract Index Position From List mysql-index-position1

The above query returns results as “My SQL”. From the set of values available, it returns the 3rd value which is My SQL. It returns NULL if the first argument is 0, less than zero, or greater than the number of total arguments

1
SELECT ELT(0,'SQL','ORACLE','My SQL','SQL SERVER') AS ColumnName;

MySQL - ELT() and FILED() Functions to Extract Index Position From List mysql-index-position2
Here are another two examples which will return the result as a null value.

1
SELECT ELT(31,'SQL','ORACLE','My SQL','SQL SERVER') AS ColumnName;
1
SELECT ELT(-5,'SQL','ORACLE','My SQL','SQL SERVER') AS ColumnName;

All the above return NULL value.

FILED() function is just opposite of ELT(). It accepts many arguments and returns the position of the value in the array specified as the first value

1
SELECT FIELD('My SQL','SQL','ORACLE','My SQL','SQL SERVER') AS ColumnName;

MySQL - ELT() and FILED() Functions to Extract Index Position From List mysql-index-position3

The above returns 3 as the value specified in the first argument “My SQL” is available at the 3rd position in the array.

It returns 0 if the value is not specified in the array

1
SELECT FIELD('COMPUTER','SQL','ORACLE','My SQL','SQL SERVER') AS ColumnName;

MySQL - ELT() and FILED() Functions to Extract Index Position From List mysql-index-position4

The result is 0.

Leave a comment »

What is a Self Join?

Question: What is a Self Join?

Answer: A self-join is simply a normal SQL join that joins one table to itself. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.

Question: Is Self Join Inner Join or Outer Join?

Answer: A self-join can be an inner join or an outer join or even a cross join. A table is joined to itself based upon a column that have duplicate data in different rows.

Question: What is a practical use of the Self Join in the real world?

Answer: The best example of self join in the real world is when we have a table with Employee data and each row contains information about employee and his/her manager. You can use self join in this scenario and retrieve relevant information. Let us see an example, over here.

Let us first create the same table for an employee.

One of the columns in the same table contains the ID of the manger, who is also an employee for the same company. Now all the employees and their managers are present in the same table. Let us see how Self Join works in the real world scenario now.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger'NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO

What is a Self Join? Explain with Example - Interview Question of the Week #064 selfjoini1

Let us now connect the Employee table with itself with the help of INNER JOIN.

1
2
3
4
5
6
-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

What is a Self Join? Explain with Example - Interview Question of the Week #064 selfjoini2

In the result set, we can see that all the employees who have a manager are visible. Though the above solution has one limitation. The limitation is that we are not able to find out the top manager of the company in our result set. Inner join does not display any result which does not have a manager id in our scenario.

Next let us convert Inner Join to Outer Join and then see the result set.

1
2
3
4
5
6
-- Outer Join
SELECT e1.Name EmployeeName, ISNULL(e2.name, 'Top Manager') AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

What is a Self Join? Explain with Example - Interview Question of the Week #064 selfjoini3

Now we have converted Inner Join to Outer Join for the same table and we can see Top Manager in resultset.

I hope it is clear from the example that SELF JOIN can be INNER JOIN or OUTER JOIN.

Leave a comment »

MySQL – How to Drop Table If Exists in Database?

In this post SQL Server – 2016 – T-SQL Enhancement “Drop if Exists” clause, we have seen the new feature introduced in SQL Server version 2016. MySQL already has this feature for a while and if you are MySQL DBA, you may find it interesting that SQL Server just introduced this feature.

Let us see this example in action now. First, we will create a sample table.

CREATE TABLE test(id INT,name VARCHAR(100));
If we have to drop above table, we can execute following script and drop it.

MySQL - How to Drop Table If Exists in Database? mysql-drop1

DROP TABLE IF EXISTS test;
MySQL – How to Drop Table If Exists in Database? mysql-drop1

The above script drops the table named test if exists, otherwise it will display a warning and not an error. This is very important to understand that as it is not displaying error, we can easily work around the warning in our code.

This is helpful if you are not sure the existence of the table in advance.

MySQL - How to Drop Table If Exists in Database? mysql-drop2

MySQL – How to Drop Table If Exists in Database? mysql-drop2

Not only tables, but MySQL has similar feature available for stored procedures as well. Let us see a quick example for the same.

Here is a script which you can use to create stored procedures.
CREATE PROCEDURE test_proc
AS
BEGIN
SELECT 1 AS number;
END;
After creating the above procedure you can use the following script to drop it.
DROP PROCEDURE IF EXISTS test_proc;
Remember, just like table, it will drop the stored procedure if there is one exist, otherwise, it will just return a warning. It is really interesting to see MySQL already having this feature for a while, whereas SQL Server just introduced this feature in this year.

 

Leave a comment »

SQL SERVER – Template Browser – A Very Important and Useful Feature of SSMS

Template Browser

Leave a comment »

Sql Server: Alter Table in Design

Many a time while making changes to the definition of the Sql Server Table we get the following message:
“Saving not permitted. The changes you have made require the following tables to be dropped and re-created or enable option Prevent saving changes that require the table to be re-created. ”

Image

To disable the option “Prevent saving changes that require the table to be re-created”
Go to Tools > Options > Designers > “Un-check” “Prevent saving changes that require the table to be re-created”

Sql Server Hack-Save Changes

Leave a comment »

SSIS Task: For loop Vs For Each Loop

For Loop

For Loop is used for looping through a number of tasks for a set number of times.

Simply:->  initialize, verify condition, increment\assign

For Each

ForEach Loop loops through various things such as files, objects, ADO connections, etc.

Above screen shot show we have task configured for “Foreach file enumerator” which can be used to move\copy\delete file (of given file) in a given folder

Enumerator configuration:

Folder: Specify the folder path from where we need to process the files

Flies: File name (format) \ file extension

  • Fully qualified: File name along with location is returned .Eg:  C:\Example.txt

  • Name and extension: The file name with its extension is returned.Eg: Example.txt

  • Name only: The file name without its extension is returned.Eg: Example

Variable Mapping

Result of the for loop will be assigned to variable and its value can be used further.

  • Foreach File Enumerator: Enumerates files in a folder

  • Foreach Item Enumeration: Enumerates items in a collection, such as the executables specified in an Execute Process task.

  • Foreach ADO Enumerator: Enumerates rows in a table, such as the rows in an ADO recordset.

  • Foreach ADO.NET Schema Rowset Enumerator: Enumerates schema information about a data source.

  • Foreach From Variable Enumerator: Enumerates a list of objects in a variable, such as an array or ADO.NET DataTable.

  • Foreach NodeList Enumeration: Enumerates the result set of an XML Path Language (XPath) expression.

  • Foreach SMO Enumerator: Enumerates a list of SQL Server Management Objects (SMO) objects, such as a list of views in a database.

Leave a comment »

SSIS: Derived column : Conditional Operator + Hardcoding Null

Using Conditional Operator In SSIS:

«boolean_expression» ? «when_true» : «when_false»

To hardcode Null : NULL(DT_WSTR, 50)

Following is the expression to get the sub string and compare to “N” and return NULL if true

else the sub string string itself.

(DT_I4)((SUBSTRING(LTRIM(Answer),1,1) == “N”) ? NULL(DT_WSTR, 50) : SUBSTRING(LTRIM(Answer),1,1))

Leave a comment »

SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation

Fragmentation – De-Fragmentation.. Must Read..

1 Comment »

SqlCmd : Error Cannot generate SSPI co ntext

Got the following error while trying to connect to SqlCmd of local machine.

Error Cannot generate SSPI co ntext

Searching online for the solution figured out that it has to do something with the network.

Workaround: Disable the network and try running the same command it works like charm.

Looking for the exact issue and better solution. Feel free to comment.

Leave a comment »

Sql Server: Using Sqlcmd to export data to text file

A SqlServerCentral friend was looking for a option to export data from Sql Server to a text file on periodic option. Refer the link below for details:

http://www.sqlservercentral.com/Forums/Topic1452185-1550-1.aspx
Adviced him to run the following sqlcmd in the job..
SQLCMD -S YourSqlServer -d YourDatabase -U YourUserName -P YourPassword -Q “Your Query” -s “,” -o “C:\YourFileName.txt”
Feel free to post better work around.
I understand SSIS could be another option.

Leave a comment »