Merge Statement in SQL Server

MERGE feature is introduced by Microsoft from SQL Server 2008 onwards.

MERGE statement allows you to perform INSERT, UPDATE & DELETE command with a single statement, and so is very efficient way to perform multiple DML operations.

In earlier version of SQL Server, we had to write multiple DML statements for INSERT, UPDATE & DELETE.

Let's take an example to understand how MERGE statement works.

In the below example we are updating the data from source table data to target table.

For this we are going to write MERGE statement, that would be performing INSERT, UPDATE & DELETE with a single MERGE statement

MERGE statement performing below operations

• UPDATE: If id matched and name not matched, updating name in target table from source table.
• INSERT: If id not matched in target table inserting row from source table to target table
• DELETE: If id in target table not matched with source table, deleting row from target table

declare @source table( id int, name varchar(50))
declare @target table( id int, name varchar(50), status varchar(10))

insert into @source
values (1, 'abc'), (2,'pqr' ), (3, 'xyz')
insert into @target(id, name)
values (1, 'abc'), (2,'sdfdf'), (4, 'abc')

select * from @target

merge @target as a
using (
    select * from @source
) as b on a.id = b.id
when matched and a.name<>b.name then update set a.name = b.name, a.status = 'updated'
when not matched by target then insert (id, name, status) values (b.id, b.name, 'inserted')
when not matched by source then delete;

select * from @target

OUTPUT

Let's Compare the data of target table before and after merge statement.

• Name updated with id 2
• Row inserted with id 3
• Row deleted with id 4

Using Variable in "IN" clause in SQL Server

In SQL Server, if you pass a comma separated list as a variable in IN Clause in T-SQL, it would not give error but you will not even get expected result either.

I have seen most of the developers facing the problem while using variable in "IN" clause in SQL Server in early stage of their career. So, I decided to post an article on how to overcome this with example.

Let's first see the problem encountered while using variable in "IN" clause.

First create a table with some sample data

create table tbldata(   
    id int, name varchar(50)
)   
insert into tbldata
select 1, 'Sandeep' union all
select 2, 'Abhay' union all
select 3, 'Ritesh'

Now, let's execute the below queries with and without variable in "IN" clause.

select * from tbldata where name in ('Sandeep', 'Abhay')

OUTPUT

Here, we get the result as expected, now let's execute query with variable

declare @idList varchar(max)
set @idList = '''Sandeep'', ''Abhay'''
select * from tbldata where name in (@idList)

OUTPUT

See here when we execute the same query with variable, we did not get the expected output. Hence, it is concluded that variable does not work fine when used inside IN clause in Sql Server.

Now, lets see the work around to this problem. There are two solutions to handle the same

1. Dynamic query

declare @query nvarchar(max), @idList varchar(max)
set @idList = '''Sandeep'', ''Abhay'''
set @query = 'select * from tbldata where name in (' + @idList + ')'
exec sp_executesql @query

OUTPUT

2. Split function
First create split function using link:  Split Function in SQL Server
Once split function is created, then use the below solution

declare @idList varchar(max)
set @idList = 'Sandeep,Abhay'
select * from tbldata where name in (select val from dbo.split(@idList,','))

OUTPUT

Replacing IN with JOIN
Split is a tabular function which returns table of the list passed to it.
In the above example IN can be replaced with JOIN

declare @idList varchar(max)
set @idList = 'Sandeep,Abhay'
select  t.*
from    tbldata t
inner join dbo.split(@idList,',') s on t.name = s.val

OUTPUT

Import Images to Sql Server using SSIS

In this article, I am going to demonstrate step by step to create a package in SQL Server Integration Services (SSIS) to import Images in SQL Server.

Step 1: Create a Flat File named ListImages.txt containing list of paths of images to be imported in SQL Server

Content of ListImages.txt

C:\Images\Image1.gif
C:\Images\Image2.gif

Step 2: Create a Table to store Images in SQL Server Database

CREATE TABLE dbo.tblImages(
    id int IDENTITY(1,1) NOT NULL PRIMARY KEY
    , imgPath varchar(200) NULL
    , image image NULL
 )

Step 3: Run SQL Server Business Intelligence Studio and create a New SQL Server Integration Project ImportImages. This will open Control Flow Task Tab by default

Step 4: Drag and drop the Data Flow Task from the Toolbox to the Control Flow Task.

Step 5: In the design pane Right Click on Data Flow Task and then click Edit or Double Click the Data Flow Task. This will open Data Flow Task Tab.

Step 6: From the Toolbox drag and drop following components on Data Flow Task Tab

• Flat File Source
• Import Column
• OLE DB Destination. 

Join the components with the green arrows as shown below.

Step 7: Right Click on Flat File Source and then click Edit.

Step 8: In the Flat File Connection Manager, press New.

Step 9: In the General Option, mention

• Connection Manager Name
• Description (Optional) 
• Browse Flat File ListImages.txt created in Step 1.

Step 10: Select Advance option and change the name to ImagePath. Click OK twice to save the settings.

Step 11: Right Click on Import Column and then click Edit. In the new window, select Input Columns Tab and Select Name

Step 12: Select Input and Output Properties Tab and expand Import Column Output from the TreeView. Select Output Columns and click on Add Column. Get the ID property value of the column created. (In this example, ID is 58)

Step 13: Expand Import Column Input from the TreeView and select ImagePath. In Column Properties, set FileDataColumnID value to 58 (generated in step 12) and Click Ok to save the settings

Step 14: Right Click on OLE DB Destination and then click Edit

Step 15: In the OLE DB Destination Editor Window, press New for the OLE DB connection manager.

Step 16:. In the Configure OLE DB Connection Manager press New...

Step 17: In the Connection Manager, select Native OLE DB\SQL Server Native Client as provider and provide other details like Server Name, Authentication Type, Credentials and Database Name to connect to SQL Server Database and Click Ok.

Step 18: In the OLE DB Destination Editor Window, In Connection Manager Option select the table tblImages.

Step 19: Select Mapping Option and do the mapping as shown below and Click Ok.

Step 20: At this point, we are done with creating the package and ready to run the project. If everything is OK, all the tasks should be displayed with green color and the number of rows imported should be displayed.

Step 21: To verify the data is imported successfully, Open SQL Server Management Studio and connect the database and open the table tblImages