Insert or Update a File in Database – SQL Server

February 17, 2016 Leave a comment

Database is a repository for data of various formats. In the past, we use text files to store huge data and technology revolution has changed everything for what we store and How we store data. Big data is a good example. It happened in different forms as punch card, file system, relational database, graph db, NoSQL, NewSQL and etc.

But, in this blog post I am going to show a simple example for storing (insert) and altering (update) a binary image file stored in a table.


Create a Table

CREATE TABLE [dbo].[Images](

Sample Image file – SQL2016.jpg


Insert a record with a Image file

INSERT dbo.[Images]
(ImageDesc, ImageData)

Update an existing record with Image file

UPDATE dbo.[Images]
SET ImageDesc = ‘SQLServer’
,ImageData = BulkColumn from Openrowset( Bulk ‘C:\Test\SQLServer.jpg’, Single_Blob) as Img
WHERE ImageID = 1

This blog post is for code reference. You can use this code with not restriction.

Categories: SQL Server

The final edition DWH and BI reference

January 10, 2016 2 comments

Ralph Kimball and the Kimball Group have continued to refine their methods and techniques based on thousands of hours of consulting and training. This Remastered Collection of The Kimball Group Reader represents their final body of knowledge, and is nothing less than a vital reference for anyone involved in the field.

  1.  Learn data warehousing and business intelligence from the field’s pioneers
  2. Get up to date on best practices and essential design tips
  3. Gain valuable knowledge on every stage of the project lifecycle
  4. Dig into the Kimball Group methodology with hands-on guidance

Download this book


Categories: Book, Data Warehouse

Connect to SQL Server in R without ODBC DNS connection

September 25, 2015 3 comments

In the previous post, I have explained the steps to connect to SQL Server with ODBC DSN.

In this blog post, I am going to connect to SQL Server without ODBC DSN. But, will be using driver name instead.

Step 1: RODBC must be installed in R. Please read (Connect to SQL Server in R for detail)

Step 2: Load the RODBC library


Step 3:Connecting to SQL Server with Driver name

MSConnection <- odbcDriverConnect(“DRIVER=SQL SERVER;SERVER=.;Trusted_Connection=Yes”)

This connection point to the default database initially. But, we can run USE statement to change the database.

sqlQuery(MSConnection, “USE test”)

Step 4: Read the table data to a variable

mydata <- sqlQuery(MSConnection, “Select * from dates”)

Step 5: Display data by just typing the variable name in R command environment


Step 6: Close the connection


RODBC documentation

Categories: R Tags: ,

Connect to SQL SERVER in R

September 20, 2015 1 comment

R is an OPEN SOURCE software environment for statistical computing and graphics. It is easy to use and create valuable data visualization in a minute. Learn more about R.

This blog post is to explain the steps to connect to SQL Server database in R and display simple graphics.

Over all steps

  1. Create ODBC Data Source
  2. Setup R environment to connect to SQL Server
  3. Query the SQL table, display data and graphics

Step 1: Create ODBC Data Source:

Goto, Control Panel -> Administrative Tools -> Data Sources (ODBC)


Click Add button


select SQL Server Native Client and click Finish


Give data source name and SQL Server name to connect and Click Next


Select an appropriate SQL Server authentication option and give valid credential. In this example, we are using Integrated Windows Authentication. Click next


Select the database to work with and modify the application intent option to Read-only. These settings are for this exercise only. We may use default setting and click next.


Click finish and TEST connection


Step 2: Setup R environment to connect to SQL Server.  Please install R software.

Install RODBC package to R:

  • install.packages(“RODBC”)

Load the RODBC library to current environment to use:

  • library(RODBC)

Connect to SQL Server using ODBC connection:

  • wrpt <-odbcConnect(“weatherdata”)

We have just connected to SQL Server and the R connection variable name is wrpt. We can use this variable to query a table and more.

Step 3: Query the SQL table to display data and graphics

This statement loads the entire table “tblTemp” in to the variable weather.

weather <- sqlFetch(wrpt, “tblTemp”)  

This statement displays the entire table


This statement displays the summary of the data-set



This statement displays a graph with daily max temperature for a  month

plot(weather$maxtemp, type=”l”)


R manuals

Thanks for reading this post.

Categories: R Tags:

How to debug T-SQL statement in SQL Server Management Studio

September 1, 2015 Leave a comment

Debugging is an important feature for every programming language with no exception SQL Server has this to help developer trace the their code and remove potential logical issue in the code before moving to production environment or simply to find the bug in the code.

SQL Server management Studio (SSMS) provide easy way to debug T-SQL statements.

Expectation: This blog post is to show how to use debug feature in SSMS with simple T-SQL statement.


declare @i as int, @n as int;
set @i = 0;
set @n = 5;

while @i < @n
set @i = @i + 1;
print @i;

Step 1: Open new query window and paste the above code

Step 2: Click Debug button on the toolbar


Step 3: Once debug starts, another debug related toolbar and windows (local variables, call stack) will show up. We can view and modify local variables and parameters, view global variables, as well as control and manage breakpoints while debugging.


Step 3: Click Step into (F11 – function key) to execute step by step.

This image is captured after 3 iterations. so, we can see the value of the variable @i = 3 and @n =5.


Try other debugging features in SSMS and keep  your code robust. Thanks.


Categories: SQL Server Tags:

Synchronize Table – SQL Server

September 1, 2015 2 comments

We have been doing table synchronize for many years using various techniques. Merge statement in SQL Server is not new for us. But, I liked this statement. So, I decided to write a simple note about it.

Expectation: This blog post is for beginners those who want to learn about this Merge statement using simple examples.

We can perform insert, update, or delete operations on a target table based using single statement based on the results of joining source and target table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. – MSDN reference

Creating working environment:

This is the main (target) table that we are going to use for synchronization.

declare @emp as table
EmployeeId int not null primary key,
DepartmentId int not null,
EmployeeName varchar(50) not null

This is the stage table to hold the data for sync.

declare @emp_stg as table
EmployeeId int not null,
DepartmentId int not null,
EmployeeName varchar(50) not null

Loading few rows to the stage table.

Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (1,1, ‘Robert’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (2,2, ‘Bobby’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (3,2, ‘Dan’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (4,3, ‘Johnson’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (5,4, ‘Monti’);

Now, using merge statement we are going to insert new rows to the main table @emp.

MERGE @emp as tgt
USING @emp_stg as src
ON src.employeeid = tgt.employeeid
INSERT  (employeeid, departmentid, employeename)
VALUES (src.employeeid, src.departmentid, src.employeename)
OUTPUT $Action, inserted.*

$Action column will have the DML operation string value such as ‘INSERT’ or ‘UPDATE’ or ‘DELETE’. inserted.* will give us the news inserted row(s).

Now, We are now going to update the rows if there is a change in the incoming data.

This is a simple DML update statement for this experiment.

UPDATE @emp_stg set EmployeeName = ‘robert k’ where EmployeeId=1;

MERGE @emp as tgt
USING @emp_stg as src
ON src.employeeid = tgt.employeeid
(src.departmentid <> tgt.departmentid or
src.employeename <> tgt.employeename)
UPDATE SET tgt.departmentid = src.departmentid,
tgt.employeename = src.employeename
OUTPUT $Action, inserted.*

Following merge statement is to perform table sync with delete operation for the unavailable data.

DELETE from  @emp_stg where EmployeeId=3;

MERGE @emp as tgt
USING @emp_stg as src
ON src.employeeid = tgt.employeeid
OUTPUT $Action, deleted.*

I have separated Insert, Update and Delete operation for this example to show the flexibility of the Merge statement. But, These statements can be used together to perform all in one operation.

Categories: SQL Server Tags: ,

Power BI Desktop

July 28, 2015 Leave a comment

Power BI is the latest hot top in the BI community in recent days. I download and install the Power BI Desktop tool in my machine and exploring now. I am amazed with the performance of this tool. As always Microsoft has done a wonder job. It is lighter and faster. Having ability to connect with different data source, transformation feature and report. I would say, it is all in one tool.


This is the final dialog box before entering into a new world.

Download a free copy of desktop powerbi

I have created a quick map report using Power BI desktop


I am planning to write more about this tool in coming days.

Categories: PowerBI Tags:
%d bloggers like this: