SQL SERVER – DENSE_RANK() – ORDER BY SOME COLUMN BUT WE SHOULD ADD WHERE FILTER WHILE ASSIGNING RANK
--drop table #TEMPCASE DECLARE @StudentDetail TABLE ( -- ID int identity(1,1), StudentCode VARCHAR(10), SchoolCode VARCHAR(10), StartDate DATE ) INSERT INTO @StudentDetail VALUES ('C00001', 'P00001', '1-Sep-2014') INSERT INTO @StudentDetail VALUES ('C00001', 'P00002', '1-Oct-2014') INSERT INTO @StudentDetail VALUES ('C00001', 'P00003', '10-Oct-2014') INSERT INTO @StudentDetail VALUES ('C00001', 'P00004', '25-Oct-2015') INSERT INTO @StudentDetail VALUES ('C00002', 'P00001', '1-Oct-2014') INSERT INTO @StudentDetail VALUES ('C00002', 'P00002', '1-Nov-2014') INSERT INTO @StudentDetail VALUES ('C00002', 'P00001', '1-Oct-2015') INSERT INTO @StudentDetail VALUES ('C00002', 'P00002', '1-Nov-2015') INSERT INTO @StudentDetail VALUES ('C00003', 'P00002', '1-Nov-2015') select * from @StudentDetail select ROW_NUMBER() over(order by (StudentCode)) as ID, ROW_NUMBER() over(partition by StudentCode order by StartDate) as RowID,cast(Null as int) as RankID,* INTO #TEMPCASE from @StudentDetail order by 1 --SELECT * FROM #TEMPCASE declare @i int=1,@count int=(select MAX(RowID) from #TEMPCASE) declare @RankID int while @i<=@count Begin set @RankID=(select MAX(isnull(RankID,0)) from #TEMPCASE) --IF exists(SELECT 1 FROM #TEMPCASE where RankID is null) -- CONTINUE -- ELSE -- BREAK update tt set RankID=@RankID+t.RankID from #TEMPCASE tt join ( select *,DENSE_RANK() over(order by StudentCode) as RankID from ( select distinct t1.ID,t1.StudentCode from #TEMPCASE t1 join #TEMPCASE t2 on t1.StudentCode=t2.StudentCode where t1.RankID is null and t2.RankID is null and t1.RowID=1 and t2.RowID>1 and DATEDIFF(dd,t1.StartDate,t2.StartDate)<=90 union all select distinct t2.ID,t2.StudentCode from #TEMPCASE t1 join #TEMPCASE t2 on t1.StudentCode=t2.StudentCode where t1.RankID is null and t2.RankID is null and t1.RowID=1 and t2.RowID>1 and DATEDIFF(dd,t1.StartDate,t2.StartDate)<=90 union all select distinct t1.ID,t1.StudentCode from #TEMPCASE t1 where t1.RankID is null and not exists (select 1 from #TEMPCASE t2 where t1.StudentCode=t2.StudentCode and t2.RankID is null group by t2.StudentCode having COUNT(*)>1) ) t ) t on tt.ID=t.ID update t1 set RowID=t.RowID from #TEMPCASE t1 join( select ID, StudentCode,ROW_NUMBER() over(PARTITION by StudentCode order by StartDate ) as RowID from #TEMPCASE where RankID is null ) t on t.ID=t1.ID set @i=@i+1 End SELECT * FROM #TEMPCASE
I need to load very fastly some sample data to database table with Sequential Numbers. We can load Sequential Numbers with Loops but it is a performance issue because it will load one record in one iteration . What is an alternative for loops to load Sequential Numbers data into table ?
By using iteratative Common table exepresion(CTE) , we can able to get the results very fastly . In the below query every CTE will sequres the amount of result come from previous CTE results.
SSIS – Combination of Parent Package Configuration and SQL Server Package Configuration along with Environment variable
As explained in this post SQL Server Package Configuration,we can able to make use of SQL Server configuration table values inside SSIS packages by using Environment variables and SQL server configuration . In SSIS Configuration table , it is storing one record for one package variable. If i have 20 + packages , which are Source and destination connections. then we have to create 40 + configurations in SISConfiguration table , even though all source and destinations are same because SSIS Configuration table is saving configurations based on Package variables.
By using Parent Package variable we can able to use only two records in SQL Server Configuration table instead of each package connection string i.e. one record for Source connection and another for Destination Connection.
What is Parent Package Variable
If a package calls another package using execute package task, then calling package is referred as Parent package and called package is referred as child package. When parent package variable is to be passed to a child package then it is called Parent Package Variable configuration
Sample Package Development :
To explain this , I have created two Packages . One is called as MasterPackage and Another one is called ChildPackage.
Master Package Setup :
In Master Package , i have taken simple execute package task and configure like below . Choose Location of package in File System and Browse child Package and Click on OK to complete .
Set Up Environment Variable in Master Package :
Screenshot 1 : Right Click on Control flow task window and choose Package Configuration tab
Screenshot 3 : Click on Environment Variable drop down and Choose previously created SSIS_Config_DB variable
Set Up SQL Server Configuration in Master Package :
Screenshot 1 : Right Click on Control flow task window and choose Variables and created two variables SourceCon and DestCon. In this two variables , values inside SQL Server Configuration tables will be copied by using SQL Server Configuration
Screenshot 2 : By using ConfigConnection manager ,Value of Souce Column inside SSISConfiguration Table will be Copied to package variable SourceCon and Repeated same steps for DestCon also
Child Package Set Up :
Following steps enable passing a parent package variable to child package:
1. Add a configuration to the child package
2. Select its configuration type as Parent Package Variable
3. Give the name of the parent package variable as 'SourceCon'
4. Click Next and select the Child package SourceConnection ConnectionString as shown in below (by doing this, the Child packageSourceConnection ConnectionString's value
gets overwritten with the value of parent package variable).Continue for DestCon also.
After we develop SSIS Packages, if we want to move developed packages to another server/ environment ,we need to change so many configuration settings in each SSIS Packages like DB Connection strings, File path and etc. But there is so much of work around this task. How we can take the advantage of the SQL Server Package Configuration which are providing by SSIS ?
SSIS offers serveral types of package configurations like environment variables,Parent Package and XML Configurations and SQL Server Configuration . In all types of configurations , parameter values are storing outside of SSIS packages and that values are utilizing while executing packages. In this post i am going to explain an approach for implementing SQL Server based package configuration that uses an environment variable to facilitate the deployment of the packages onto different servers without having to modify the packages
What is SQL Server package configuration ?
SQL Server package configuration is nothing but storing configuration values like Connection strings ,File path etc in SQL server table. To utlize this values inside SSIS Packages, we need to create one configuration connection manager inside SSIS package which tells, in which server and which database this SSIS Package Configuration table is exists. lets call this connection name as 'ConfigConnection'.
What is Environment Variable ?
Environment Variable is an variable created at machine level(Operating System level) and we can give the value to the Environment Variable and can use this value inside any of the program which are runing in the machine . In our example we will use SSIS_CONFIG_DB Environment Variable inside our SSIS package. So i have created one SSIS_CONFIG_DB Environment Variable at machine level and given below value
Data Source=localhost;Initial Catalog=SSISConfig;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
We will see later in post how to create Environment Variable and edit value in this Variable
What is need of environment Variable to make use of SQL Server package configuration ?
As i explained above, to get the configuration values of SQL Server table , we have to create one connection (ConfigConnection ). Since during the development we are pointing to a development DB instance, we need to change the connection information point to the QA or Production source databases without editing the package each time.
To notify what is connection string of ConfigConnection to SSIS package , we have to use environment variable which is already having the connection string value This allows for the package to point to configuration table based on Environment (dev, QA, test, prod, etc).Each configuration table shall have the configuration values that are appropriate to each environment.The environment variable shall exists in all machine where you intend to deploy the package, and its content should be updated to accordingly.
Advantages of SQL Server package configuration over the other package configurations :
- DBAs are usually more comfortable working with SQL Server tables than XML files.
- Storing package configuration values in a SQL Server database rather than on the file system
- Simple T-SQL commands such as INSERT, UPDATE and DELETE.
Sample Package Development :
Let’s assume we have a package that has a connection manager called ‘SourceConnection’, which has the connection information of the source DataBase where the package is trying to retreive data from. Also we have one more connection manager called ‘DestinationConnection’ where the data is going to be load.
Screenshot 1: Sample Package which has one Data flow task , two OLEDB connections i.e. one Source Connection and one destination connection
Screenshot 3 : Right Click on Control flow task window and choose Package Configuration tab
Screenshot 5 : Click on Environment Variable drop down and Choose previously created SSIS_Config_DB variable
Screenshot 11: Repeat same steps for Destination Connection Also
In Software World, Source of Knowledge is requirementJ.
Today I got big SQL Script (600 MB) to execute in SSMS. But due to big file it was not opened in SSMS. So I was searching for solution to execute this file without opening .sql file in SSMS, I found solution in by using sqlcmd we can able to run a Transact-SQL script file. By using SQLCMD we can execute any type SQL Scripts.
To demonstrate, how to use SQLCMD. I have taken simple SQL Script and saved in Notepad and given name as SQLCmdSample.sql and Save the file in D Drive
I always likes logical work than regular boring work. So when I am browsing on Google, I found some interesting question like how to delete all tables’ data in SQL Server except top N records in each table?
Here is the script for delete all tables data SQL Server DB except top N records in each table
Considering all tables do not have any inter dependency on each other