MSSQL : Import CSV file into a Table

Recently, I am working on the data stored in the Excel files. Those data are needed to be imported into the database in order to allow the stored procedures to execute and tabulate data back to the users. During the process of importing the Excel files using the “Import Data” (Right click the database > Task), I found the process was tedious because of setting the correct data type for each columns and always cause me to have an error related to truncation text.

Since, the Excel files have a lot of columns and rows, I decided to try another method to import the Excel data into the table, by using the BULK INSERT method. This method requires the Excel files to be in .csv format to begin with. It is easy to convert the .xlsx into .csv file.

Below is the script on how to use the “BULK INSERT” script. Beforehand, a table called dbo.upload_file is created. Based on the delimiter set in the local machine, place the value accordingly at the “FieldTerminator”.

BULK INSERT [dbo].[upload_file] FROM 'C:\Users\<filename>.csv'
    WITH (
       FIELDTERMINATOR = ',',
       ROWTERMINATOR = '\n'
 );

The nightmare began when I realized the data in the Excel contains a lot of comma (,). The data cleaning process began by removing them altogether from the Excel file. Besides that, you want to check if you have inserted the header into the database’ table.

We can set the first row to be inserted into the table.

BULK INSERT [dbo].[upload_file] FROM 'C:\Users\<filename>.csv'
    WITH (
       FIRSTROW = 2,
       FIELDTERMINATOR = ',',
       ROWTERMINATOR = '\n'
 );

By default, Excel saves the .csv file using delimiter comma (,), however it can be changed at the Control Panel setting. Open up Control Panel –> Region and Language, and then click the Additional settings button on the bottom. Now look very closely at the “List separator” item, which normally has a comma in the field, change it to your preferred delimiter such as pipes “|”. Then, hit “Apply” and “Save” button to save the changes made.

Then, next time when saving a .csv file, it will use the new delimiter. Either way can solve the issues mentioned above and the data can be inserted into the database’s table successfully.

Advertisements

MSSQL: Customize theme for SSMS Express 2014

The newer versions of SQL Server Management Studio (SSMS) allows users to change the theme easily from the option menu, however, the version 2014 has different approach and I googled online to find a method to customize it according to my preference of dark theme.

This link gives a good example of how to customize dark theme in SSMS 2014, https://blogs.sentryone.com/aaronbertrand/making-ssms-pretty-my-dark-theme/.

It is simple to follow and you can change the RBG according to your preferences without having to follow exactly every colours mentioned by the writer.

Enjoy!

Highly Scalable Systems

I was being asked to look into this topic called highly scalable systems. Instantly, I diverted my attention to my IT team for more insights. As far as I know, this topic covers many areas, it could be storage, processor cores , memory and etc, from hardware to software. Looking at the data volume nowadays, how to design a highly scalable system is important. So, from where I should start first?

Let me try to understand the term “highly scalable”.  It means flexibility to scale. What is scale? It means can change. In short, it is flexibility to change. In most of the cloud services are now scalable. 

We need to build a solution, uniquely and dedicated to the project.

Next, I moved to focus on database scaling. The first thing it prompts up is the horizontal and vertical scaling. If you have read the MongoDB document, you will find this topic too. I picked up one of the plain text explanations from the stackoverflow website.

Horizontal scaling means that you scale by adding more machines into your pool of resources whereas vertical scaling means that you scale by adding more power (CPU, RAM) to an existing machine.

https://stackoverflow.com/questions/11707879/difference-between-scaling-horizontally-and-vertically-for-databases

Usually, when we do vertical scaling, it requires some downtime to increase the resources and restart server to complete this process. Meanwhile, horizontal scaling just adding another machine in the system, it is highly used in the tech industry. This will decrease the request per second rate in each server.

To segregate the requests, equally to each of the machine (example, application server), you need to add load balancer which would act as reverse proxy to the web servers.

‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

I was working on a complex query and required a dataset to be uploaded into my local machine’s database. The file format of the dataset was excel file and I was using the SQL Server Management Studio (SSMS) to upload the dataset into the database. 

Unfortunately, the SSMS returned an error message after I have selected my source as excel file. The error message was ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.

For my case, the fixes for this error is done by installing the Microsoft Access Database Engine driver. The installation is simple, straight forward and hassle free. It does not require the machine to be restarted in order to continue importing the dataset using excel file.

Reference link: https://social.msdn.microsoft.com/Forums/en-US/1d5c04c7-157f-4955-a14b-41d912d50a64/how-to-fix-error-quotthe-microsoftaceoledb120-provider-is-not-registered-on-the-local?forum=vstsdb

[SQL] Grant User Permissions

The task which I was doing today required me to prepare a SQL script which grants the user to execute the user defined functions and stored procedures. While SQL Server Management Studio allows us to set the permission by a few clicks, it is good to prepare them using the script so we can re-use them in case we have run them in the development and production databases.

A simple script to grant user with execute permission for stored procedures. It applies to all stored procedures in the selected database.

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO <user>

Next, I found out that my application needs to set permission for the user defined functions. I felt I could have spent the same amount of time to search the script to automate the process and manually wrote the script one by one. The script below can help,

/* GRANT EXECUTE TO THE USER DEFINED FUNCTIONS */
SELECT 'GRANT EXECUTE ON [' + SCHEMA_NAME(schema_id) + '].[' + [name] + '] TO MyRole;'
FROM sys.objects
WHERE type IN ('TF','FN');

There is another script which could help,

declare @name varchar(100), @qry varchar(2000)
declare cursor cursor_temp
for select name from dbo.sysobjects where OBJECTPROPERTY (id, IsProcedure) = 1
open cursor_temp
fetch next from cursor_temp into @name
while @@fetch_status = 0
begin
set @qry = ' grant execute on [dbo].[' + @name + ' ] to [ <user_name> ]'
exec (@qry)
fetch next from cursor_temp into @name
end
close cursor_temp
deallocate cursor_temp

I hope this could help.

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.

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.