Experience Sharing, SQL Databases

SQL Server Export Data into Excel

How to export data from the SQL Server Management Studio into Excel file?

I used to save the data into .csv file and transfer the file out into my local machine via FTP or use the copy and paste command to transfer the data into the Excel directly from the database. This happened when you have control on the servers and security is not a big concern. Another method is using the SSIS.

Due to various reasons, my client did not allow installation of Excel into the database server and recommended me to use the Export Data function in the SSMS. I seldom used the Export Data function, anyway. Instantly, I gave a try after I was guided.

The steps are simple, guided and straight forward to follow. Here is the step:-
1. Right click the database > Task > Export Data.

2. Choose a data source. It means where the source location is. For this example, we select SQL Server Native Client 11.0. Enter the user credentials if you are using SQL authentication, otherwise, you can just continue with Windows Authentication.

Select the database which you want to export the data from.

3. Choose a destination. It means select the location where you want to save or export the data to. Destination selection, we choose Microsoft Excel, then enter the Excel file path where you want to save the file at. As for the Excel version, I believe it is based on the driver version we installed. If you want to keep the first row with column names, please check the checkbox.

Then, we can proceed with Next button.

4. Specify table copy or query. Here, we can choose either to select which table or tables we want to copy all its data into the Excel or we can write a SQL query to specify the dataset to be copied or exported. Let see how we can export a table out from the SSMS.

Choose the first option, Copy data from one or more tables or views. This allows copying the entire dataset from the selected tables or views into the Excel file. Select the tables by checking the checkbox at the Source.

The destination will name the Excel sheet according to the table’s name. If you want to change the name, you can click on each destination table’s name and edit it. If you do not want to export the entire table, you can select to write a query by choosing the second option from the previous screen.

Write your SQL statement in the next screen, just like below, which I selected top 1000 rows of records from the selected table. You can rename the table name at the Destination similarly to what I describe earlier in the next screen.

The table name will appear in the Excel sheet. One sheet for one table and as you know Excel has maximum number of rows, 65,536 for Excel 2003 and 1,048,576 for Excel 2007. I believe there is no concern about maximum column.

Next, we move to the Edit Mappings… button which is important to look into it especially when we want to format the Excel’s cell format.

In the Edit Mappings, you can select the data type of each field by clicking the dropdown selection list under the Type column. And, click OK to save the mappings. Then, you can view the dataset by clicking on the Preview button. A limited row of data will display in the next screen.

After editing the mappings and preview the dataset, it is time to review the data type mapping

5. Review Data Type Mapping. Click Next button to proceed and run the package.

6. Run Package. Nothing much you can choose, either run immediately or cancel the process. Click Next or Finish button to complete the wizard, it will run the package and save data into the Excel file.

Lastly, navigate to your Excel file path to retrieve the Excel file and check the data inside it.

Advertisements
Experience Sharing, SQL Databases

SQL Server – Types of JOIN

Type of JOIN is quite often being asked during the interview where we are asked to list out the type of JOIN and the differences of each of them. I found a simple illustration of the JOIN command to give the first glance idea of what JOIN is.

Type of JOIN includes:-
1. INNER JOIN
2. LEFT JOIN (LEFT OUTER JOIN)
3. RIGHT JOIN (RIGHT OUTER JOIN)
4. FULL JOIN  (FULL OUTER JOIN)
5. CROSS JOIN

The picture above explains the results set to be returned when these type of JOIN are used in our queries. Next time, whenever there is a confusion of which JOIN command to be used, refer to this image to clarify our doubts.

However, let us have some short description of each command too, for our better understanding.

1. INNER JOIN
Returns all rows for which there is at least one match in BOTH tables.

2. LEFT JOIN
Returns all rows from the left table, and the matched rows from the right table.
Eg: The results will contain all records from the left table, even if the JOIN condition does not find any matching records in the right table, but with NULL in each column from the right table.

3. RIGHT JOIN
Returns all rows from the right table, and the matched rows from the left table. It is exactly opposite of the LEFT JOIN.
Eg: The results will contain all records from the right table, even if the JOIN condition does not find any matching records in the left table, but with NULL in each column from the left table.

4. FULL JOIN
Returns all rows for which there is a match in EITHER of the tables.
Eg: Its result set is equivalent to performing a UNION of the results of left and right outer queries.

5. CROSS JOIN
Returns all records where each row from the first table is combined with each row from the second table. Refer to the image below which I captured from the explanation made by Pinal D. in his blog.

Experience Sharing, SQL Databases

SQL – NULL

Last year during one of the SQL Server User Group event brought by Microsoft, they invited Pinal Dave. I am sure you know this guy who wrote many SQL articles in his blog page, blog.sqlauthority.com.

One of the most interesting topics that he shared that morning was the SQL NULL. Sometimes, we have hard times dealing with the null values when we are working on our queries. During one of the interview, I was being asked about the returned result when we execute the below query.

select case when null = null then 'yup' else 'nope' end

I could not remember if I get the answer correct but I did think the query is wrong. According to my search in the Internet, the same question appeared in one of the links where the explanation is as below:-

This query will yield “Nope”, seeming to imply that null is not equal to itself! The reason for this is that the proper way to compare a value to null in SQL is with the is operator, not with =.

It suggests the correct way to compare null value is using the IS operator. You can give a try running it from the SSMS to see the returned result set which I am going to share here as well.

select case when null is null then 'yup' else 'nope' end

Next, I wish to search a bit more about NULL values.

An article from TechNet website, it says below:-

A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

Anything compared to NULL evaluates to the third value in three-valued logic: UNKNOWN. Interesting as it is, there are many more things about NULL values can be read from the websites. As for now, we know that to check the NULL values, we shall use IS or IS NOT in WHERE clause.

Experience Sharing, SQL Databases

SQL Server – Difference Between UNION And UNION ALL

I am trying to recall some of the interview questions that I was being asked during my job hunting and would like to share some studies or findings which I read from the experts regarding these questions. The first one that I remember is the differences between UNION and UNION ALL.

I believe in many cases we do use both of these commands in our queries. And, what are their differences and in term of performance which one is better?

UNION command is used in the queries to select data from two or more tables similarly to JOIN command, except UNION command selected columns need to be same data types and same number of columns to be selected. It returns the distinct data of both tables. It does the SELECT DISTINCT on the results set.

UNION ALL command is same as the UNION command except it returns all the data from the selected tables. In other words, UNION ALL includes duplicated rows.

However, the UNION ALL gives the faster results when we execute the queries. You can check it from the execution plan to compare both. Reason is UNION command will do the filtering during the execution. If you are sure that your return results are unique, it would be a good idea to use UNION ALL for better performance.