I am in the process of moving my Access database tables and update process onto SQL Server. I have been able to transfer the historical data and create tasks via SSIS to import the data regularly the issue is replicating the saved queries that were one in Access
For example I had a query that calculated multiple KPIs using SQl such as
SUM(Case when ThisField = 5 then 1 else 0))
However short of creating a query in SQL Server and saving it somewhere I cant seem to find a better answer.
I had considered using SSAS, that way all of these functions could be predefined however as the majority of this is simply on 1 table that proved difficult
Other users will need to access these queries, before they were stored within Access and could modify as they need but I am not so sure with SQL Server, what option would be best?
Thanks
I cant comment yet, but I'll give you an "answer". Stored procedures are the best way to "save" things and then you can create jobs to run them. Basically
CREATE PROCEDURE YourNewProcedureName
declare @Parameter as varchar(50) --if you need a parameter
as
Select * from yourtable
where ThisColumn = @Parameter
and then once created change the word CREATE to ALTER and execute a second time. This way, you are now just altering the existing procedure.
ALTER PROCEDURE YourNewProcedureName
declare @Parameter as varchar(50) --if you need a parameter
as
Select * from yourtable
where ThisColumn = @Parameter
Then,
execute YourNewProcedureName
in a step in a job, will trigger it to run on a schedule, should you need that functionality. You can also use the proc name in SSRS to create reporting.
As for the access queries, I believe that unfortunately you are looking at a manual code conversion. Finding someone that knows both syntaxes or learning the SQL syntax yourself are your best bet. There are things SQL can do much better, particularly if you are on a newer version.
Have fun! Hope this helps!