Sqlserver007

Problem:
A Business User wants to update/insert/delete the values in a table that sits in the metadata of the Datawarehouse on a regular basis. Based on these values numerous calculations will be done in the warehouse which generates a data driven Report on a weekly basis.

Initially I got the following Solutions
1. Create a frontend app for the user to populate the values (the most common Global solution)
2. Ask the user to populate the values in an excel file with the columns in specific format so that I can use my SSIS to do the lookup with the original table and update/delete/insert based on the values.( But there are lot of things to consider when using excel like Problems with incorrect format of excel files and maintaining them.)

So when I was reviewing the Merge feature in TSQL 2008, an idea stuck in mind

The solution I ended up doing was

Creating a Reporting services Report that takes the values as parameter and use the MERGE statement to insert/update/delete the values in the backend table

Here is the Example of what I mean:

create table CricketRuns
(
PlayerID int identity(1,1),
PlayerName nvarchar(max),
Runs int,
Average float
)
go

insert into CricketRuns
select 'Sachin Tendulkar',17000,45.23
union
select 'Ricky Ponting',12000,41.08
union
select 'Kevin Peiterson',4000,46.00
union
select 'Chris Gayle',7000,34.87
union
select 'Jacques Kallis',11000,42.09
union
select 'Ross Taylor',2500,41.00
go
 

So the table looks like

I created this procedure using the Merge Statement that will insert / delete / update based on the players name

create procedure uspInsertRuns
	(
		@PlayerName nvarchar(max)
		,@Runs int
		,@Average float
		,@status nvarchar(100)
	)
		as
	Merge into CricketRuns as Target
	using
		(select @PlayerName,@Runs,@Average,@status) as source(Playername,runs,Average,Status)
	on
		(Target.Playername = source.PlayerName)
		when matched and Source.Status = 'D' then
		Delete
		when matched then
		update set runs = source.runs , Average = source.Average
		when not matched then
		insert (PlayerName,runs,Average)  values (source.PlayerName,source.runs,source.Average);

		select PlayerName,Runs,Average from CricketRuns order by PlayerID
 

So you can insert,delete or update as below

exec uspInsertRuns ‘Andrew Strauss’,2000,35,” –> Insert
exec uspInsertRuns ‘Virendar Shewag’,5000,37,” –> Insert
exec uspInsertRuns ‘Ross Taylor’,0,0,’D’ –> Delete
exec uspInsertRuns ‘Sachin Tendulkar’,20000,45,” –> Update

Just add the proc in a reporting services and expose it to the business user and thats it..A frontend using Reporting Services

So without any excel management or creating a .net frontend reporting services has done the job.