Learn ! Share ! Repeat

Introduction  In this post we will see how we can do data validation using CASE statement.  Scenario We want to validate not only pick the records with data mismatch but also the exact columns where the data mismatch is present.  We are aware that we can make use of EXCEPT queries to find the records with data mismatch, but in case there are millions of records in a our tables with large number of columns and if our except query fetches even few thousand records, it is simply a nightmare to zero in on the exact values. We can use JOIN coupled with CASE statement to do above.  In the above scenario we have a case where we have a PK present, which we will use to JOIN and then do a column by column comparison using CASE statement to pick the data mismatches. P.S. Joining column must be present to make use of this query.  USE tempdb GO SET NOCOUNT ON GO -- This table is one which is having correct data CREATE TABLE Table...

Introduction In this post we will create a simple utility to perform these tasks:  Execute a SQL script stored as .sql file in a local directory. Save the result set as .csv. Use outlook application configured on the local machine to send the .csv as an attachment to email recipients.  We will be using PowerShell script. Lets prepare the .sql script. For this utility purpose we will make use of a very simple query. Below is the code for the .ps1 which will perform the tasks we mentioned above. $global:toMailList         = "toEmail@domain.com" ; $global:ccMailList          = "cc Email1@domain.com ; ccEmail2@domain.com " ; $global:MailSubject       = "PS outlook Test Email" ; $global:MailBody           = "Sent using Powershell via Outlook Client" ; $global:SQLScriptPath = "C:\...\SQLInputFile.sql" ; $global:Attachment       = "C:\...\TestOut...

In this post we will import data from .xlsx file into SQL Server. As with  Tips and Tricks for data import into SQL Server – Part I   , you might think this is a trivial task. You are correct ! However, in this post we will pick a case where we do have to take configuration steps w.r.t. the task at hand before we can actually import data. Our challenge is that we have and 32-bit Microsoft Office 2016 installed on our 64-bit SQL Server and we want to import data from .xlsx file. Let's start by solving one problem at a time as we start to import data from .xlsx file. Below is our .xlsx file placed at a local folder. Step 1: We will try to import using OPENROWSET query SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0' , 'Excel 12.0;    Database=C:\Users\.....\SourceFile.xlsx' , 'select * from [Sheet1$]' ) As expected, we will encounter an error. Step 2: We take a clue from the error itself. To resolve we need to enable 'A...

In this post, we will import a .csv into SQL server. While importing csv can be one of the easiest task that can come over to you as a part of your daily duties of  being a data engineer, sometimes it can require a bit more thinking and efforts. A similar thing came across, where around 50+ large .csv files in a shared location and had to be imported in SQL server and all of them had varying headers which meant destination tables were supposed to have different schemas. Though, there are numerous methods to achieve this for example you create an SSIS package or may be write a PowerShell script to loop through the files, and import them. However, we looked to use SQL Server’s bulk insert command to complete the task. If you have worked with BULK INSERT, you would have noticed that the destination table must be present beforehand. Therefore, the main challenge is that, destination tables with different schemas have to be created dynamically (on the fly) according to th...