Monday, 24 October 2016

How to Loop in SQL server?

How to Loop in SQL server? 


The below example shows how to loop in microsoft SQL server and insert records in chunks: This makes inserts faster.

SET IDENTITY_INSERT dbo.table1 ON /* This is required if you are adding data to Identity columns */

DECLARE @cnt INT = 2006;

WHILE @cnt < 2016
BEGIN
select * from table1 where [Reporting_Period_SID]= @cnt
  SET @cnt = @cnt + 1
 END

SET IDENTITY_INSERT dbo.table1 OFF

Selecting top 3 (n) or Max 3 (n) records for each group by condition


Use the example to select top 3 records for each group. The row number is broken by the Sales_Reporting_Period and then is used to filter the top 3 records.

SELECT Company_code, Run_sid, Curr_ind, Sales_Reporting_Period from
(SELECT          AF.Company_Code,
                BB.Run_SID ,
              BB.Curr_Ind
              ,BB.Sales_Reporting_Period , ROW_NUMBER()
    over (
        PARTITION BY Sales_Reporting_Period
        order by Sales_Reporting_Period, BB.Run_Sid Desc
    ) AS RowNo
         FROM
                       [dbo].[Run_Dim] BB,
                       [dbo].[My_Reserve_Fact] AF
                       Where
              AF.Run_SID = BB.Run_SID
              group by
                      AF.Company_Code,
                     BB.SalesReporting_Period
                  ,BB.Curr_Ind
                     ,BB.Run_SID)Q1 where RowNo <=3