Özgür Özvarış

Bir yazılımcının kırık dökük dünyası

MSSQL Basit Cursor - 2

clock Kasım 5, 2018 13:10 by author OzgurOzvaris

Merhaba

Dün yeni bir cursor'a daha ihtiyacım oldu,

Belki faydası olabilir paylaşıyorum.

DECLARE @email VARCHAR(250) 
DECLARE @TANIM VARCHAR(256) 
DECLARE @FCCM1_ID numeric(12,0) 
DECLARE @COUNT int
 

set @COUNT = 0
 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR FOR  
SELECT email from unsubscribed_members_export_ef5$ 

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @email
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       

	   if EXISTS (select TANIM FROM FCCM8_T WHERE TANIM = @email) 
	   begin
		
		
		select @FCCM1_ID  = FCCM1_ID  FROM FCCM8_T WHERE TANIM = @email

		IF NOT EXISTS (select FCCM1_ID from FCCM6 WHERE FCCM1_ID = @FCCM1_ID)
		begin
			insert FCCM6(FCCM1_ID,FSMUSOZEL_ID,FSOZLDEGER_ID) values( @FCCM1_ID, 79, 73)
			set @COUNT = @COUNT + 1
			print cast(@COUNT as varchar(10)) + ' '+ @email + ' ' + cast( @FCCM1_ID as varchar(10))

		end
		

	   end
 
       FETCH NEXT FROM db_cursor INTO @email
END  
 
print cast(@COUNT as varchar(10)) + ' '+ @email

CLOSE db_cursor   
DEALLOCATE db_cursor

Umarım faydası olur . İyi çalışmalar.

 



Common Table Expression (CTE)

clock Mayıs 28, 2018 16:00 by author OzgurOzvaris

Merhaba,

Kaynak : When to use Common Table Expression (CTE)

How to create Temp Table with month columns based on date parameters?

Eğer sql ile recursive bir aylar yada yıllar listesi oluşturmak isterseni bu yöntemi kullanabilirsiniz

Günler döngüsü

DECLARE @StartDate DATE = '6/1/2013';

WITH Tablo(MDate) AS (
      SELECT @StartDate AS MDate
      UNION ALL
      SELECT DATEADD(dd, 1, MDate)
      FROM Tablo
      WHERE DATEADD(dd, 1, MDate) < DATEADD(mm, 1, @StartDate)
   )
SELECT MDate FROM Tablo
--temp tabloya al
SELECT  MDate INTO #tmp1 FROM Tablo

Aylar döngüsü

declare @start_date DATE = '20160101' 

;WITH CTE AS
(
    SELECT @start_date AS cte_start_date, DATENAME(month, @start_date) AS NAME , 0 AS Coun
    UNION ALL
    SELECT DATEADD(MONTH, 1, cte_start_date), DATENAME(month, DATEADD(MONTH, 1, cte_start_date)) AS NAME , 0 AS Coun
    FROM CTE
    WHERE DATEADD(MONTH, 1, cte_start_date)  < DATEADD(yy, 1, @start_date)  
)
Select * from CTE
--temp tabloya al
SELECT  Coun,Name INTO #tmp1 FROM CTE

İyi çalışmalar.



Hakkımızda  AboneOl 

Blog Yayınımıza Hoşgeldiniz.

Month List

RecentPosts

Sign In