HOW TO CREATE WINDOWS USING T-SQL Script

1. By using system stored procedure "master.sys.xp_create_subdir" to create  windows directories(Folders).see below tsql code to create folder 


  DECLARE @foldername VARCHAR(100)
  SET @foldername='c:\createfromsql'
  EXEC master.sys.xp_create_subdir @foldername

Purge sp to print deleted count

/****************************************************************************************************************
-- SQL Script    :  ES_Purge_ContactBase.sql
--****************************************************************************************************************
-- Description    :
--------Delete all contact records in the ContactBase table that are not associated with an active policy or quote 
--------and no associated with any customer validation and interaction note data already stored in CRM

--------Inactive contacts are defined as contact records satisfying all of the following conditions:
--------1.    Contacts not associated with any active policy (eas_policy)  
--------2.    Contacts not associated with any active quote (eas_quote)  
--------3.    Contact records that are not associated with any records in the following tables:
--------a.    eas_customervalidationBase
--------b.    eas_interactionBase

--------The physical delete needs to be cascaded to the following entities based on the foreign key constraints.
--------1.    ContactBase
--------2.    eas_policybase
--------3.    eas_quotebase
--------4.    eas_policycontactbase
--------5.    eas_contact_policy_relationship
--------6.    eas_quotecontactbase
--------7.    eas_contact_quote_relationship
--------8.    CustomerAddressBase

---PID          : 15-CLM-174
-- Developed    : Babji Reddy
--created_date  : 03/14/2016
-- Reviewed by  :
-- Modified By    :
-- Release        : 16-REL-03
--********************************************************************************************************************/

--USE ENV_CRM
--GO

IF EXISTS(SELECT * FROM sys.objects WHERE object_id=object_id(N'ES_Purge_ContactBase') AND TYPE IN(N'P',N'PC'))
BEGIN
    DROP PROC dbo.ES_Purge_ContactBase
END
GO
   
CREATE PROCEDURE dbo.ES_Purge_ContactBase
(
  @number_of_purgerecords INT=NULL
)
AS
BEGIN
    BEGIN TRY;
    SET NOCOUNT ON;
    DECLARE @message VARCHAR(MAX)=''
    DECLARE @CustomerAddressBase INT=0
    DECLARE @eas_contact_quote_relationshipBase INT=0
    DECLARE @eas_quotecontactBase INT=0
    DECLARE @eas_quoteBase INT=0
    DECLARE @eas_policycontactBase INT=0
    DECLARE @eas_contact_policy_relationshipBase INT=0
    DECLARE @eas_policyBase INT=0 
    DECLARE @contactbase INT=0

    IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_main') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
    BEGIN
        DROP TABLE dbo.Temp_ContactBase_main
    END
           
    IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_sub') AND OBJECTPROPERTY(id,N'ISUserTable')=1)
    BEGIN
        DROP TABLE dbo.Temp_ContactBase_sub
    END

    CREATE TABLE dbo.Temp_ContactBase_main
        (
            ContactId uniqueidentifier NOT NULL,
            CONSTRAINT PK_Temp_ContactBase_main PRIMARY KEY CLUSTERED (ContactId)               
        )

           
    CREATE TABLE Temp_ContactBase_sub
    (
        ContactId uniqueidentifier NOT NULL, 
        CONSTRAINT PK_Temp_ContactBase_sub PRIMARY KEY CLUSTERED (ContactId)
    )
           
IF @number_of_purgerecords IS  NULL
BEGIN 
;WITH Un_wanted_contacts AS(
SELECT eas_Contact AS ContactId from eas_quotecontactbase  (NOLOCK)  where statecode=0 
UNION 
 SELECT eas_contactid AS ContactId FROM eas_policycontactbase (NOLOCk) WHERE StateCode=0
UNION 
 SELECT eas_contactid  AS ContactId FROM  eas_interactionBase (NOLOCk) 
UNION 
 SELECt eas_contactid  AS ContactId FROM  eas_customervalidationBase (NOLOCk)
UNION 
 SELECt eas_PolicyHolderNameId  AS ContactId FROM  eas_customervalidationBase (NOLOCk)
 UNION
SELECT eas_primaryinsured  FROM eas_quotebase (NOLOCK) WHERE statecode = 0
 UNION
 SELECT eas_policyholderid FROM dbo.eas_policybase (NOLOCK) WHERE statecode = 0
)
INSERT INTO Temp_ContactBase_main(ContactId)
SELECT ContactId FROM contactbase  (NOLOCK) 
 EXCEPT 
 SELECT ContactId FROM Un_wanted_contacts

 END 
    ELSE 
    BEGIN

        ;WITH Un_wanted_contacts AS(
         SELECT eas_Contact AS ContactId from eas_quotecontactbase  (NOLOCK)  where statecode=0 
         UNION 
         SELECT eas_contactid AS ContactId FROM eas_policycontactbase (NOLOCk) WHERE StateCode=0
         UNION 
         SELECT eas_contactid  AS ContactId FROM  eas_interactionBase (NOLOCk) 
         UNION 
         SELECt eas_contactid  AS ContactId FROM  eas_customervalidationBase (NOLOCk)
         UNION 
         SELECt eas_PolicyHolderNameId  AS ContactId FROM  eas_customervalidationBase (NOLOCk)
         UNION
         SELECT eas_primaryinsured  FROM eas_quotebase (NOLOCK) WHERE statecode = 0
         UNION
         SELECT eas_policyholderid FROM dbo.eas_policybase (NOLOCK) WHERE statecode = 0
        )
        INSERT INTO Temp_ContactBase_main(ContactId)
        SELECT top (@number_of_purgerecords) ContactId FROM (
        SELECT ContactId FROM contactbase  (NOLOCK) 
         EXCEPT 
         SELECT ContactId FROM Un_wanted_contacts) a
 
   END    
                              
INSERT INTO dbo.Temp_ContactBase_sub(ContactId)
SELECT TOP 10000 ContactId FROM Temp_ContactBase_main WITH(NOLOCK)


WHILE EXISTS( SELECT 1 FROM Temp_ContactBase_main)
BEGIN   
         
        --CustomerAddressBase 
        --eas_contact_quote_relationship 
        --eas_quotecontactbase 
        --eas_quotebase 
        --eas_policybase 
        --eas_policycontactbase 
        --eas_contact_policy_relationship 
        --contactBase

        SET @message=''

        ---CustomerAddressBase

        DELETE cab
        FROM dbo.CustomerAddressBase cab
        INNER JOIN dbo.Temp_ContactBase_sub cb WITH(NOLOCk)
        ON cab.ParentId=cb.ContactId
       
        SET @CustomerAddressBase=@CustomerAddressBase+@@ROWCOUNT
        SET @message=@message+'No of purge records in CustomerAddressBase:'+CAST(@CustomerAddressBase AS VARCHAR(10))
       
        -- eas_contact_quote_relationshipBase
        DELETE ecqr
        FROM  dbo.eas_contact_quote_relationshipBase ecqr
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecqr.ContactId=re_sub.ContactId

        SET @eas_contact_quote_relationshipBase=@eas_contact_quote_relationshipBase+@@ROWCOUNT   
        SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_quote_relationshipBase:'+CAST(@eas_contact_quote_relationshipBase AS VARCHAR(10)) 

        --eas_quotecontactBase
        DELETE ecqr
        FROM  dbo.eas_quotecontactBase ecqr
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecqr.eas_Contact=re_sub.ContactId

        SET @eas_quotecontactBase=@eas_quotecontactBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number of purge records in eas_quotecontactBase:'+CAST(@eas_quotecontactBase AS VARCHAR(10)) 

        --eas_quoteBase
        DELETE ecqr
        FROM  dbo.eas_quoteBase ecqr
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecqr.eas_primaryinsured=re_sub.ContactId

        SET @eas_quoteBase=@eas_quoteBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number of purge records in eas_quoteBase:'+CAST(@eas_quoteBase AS VARCHAR(10)) 
       
       --eas_policycontactBase
            DELETE ecv
         FROm dbo.eas_policycontactBase ecv       
         INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
         ON ecv.eas_contactid=re_sub.ContactId

        SET @eas_policycontactBase=@eas_policycontactBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number of purge records in eas_policycontactBase:'+CAST(@eas_policycontactBase AS VARCHAR(10)) 


      ---eas_contact_policy_relationship
        DELETE ecpr
        FROM  dbo.eas_contact_policy_relationshipBase ecpr       
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecpr.contactid=re_sub.ContactId         

         SET @eas_contact_policy_relationshipBase=@eas_contact_policy_relationshipBase+@@ROWCOUNT
         SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_policy_relationshipBase:'+CAST(@eas_contact_policy_relationshipBase AS VARCHAR(10)) 
       ---eas_policyBase
        DELETE ecqr
        FROM  dbo.eas_policyBase ecqr
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecqr.eas_PolicyHolderId=re_sub.ContactId
        
        SET @eas_policyBase=@eas_policyBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_policy_relationshipBase:'+CAST(@eas_policyBase AS VARCHAR(10)) 
        --- Contactbase
        DELETE ab
        FROM  dbo.ContactBase ab 
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ab.ContactId=re_sub.ContactId
       
        SET @ContactBase=@ContactBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number Of Purge Records in ContactBase:'+CAST(@ContactBase AS VARCHAR(10))

        DELETE ab
        FROM  Temp_ContactBase_main ab
        INNER JOIN  Temp_ContactBase_sub y WITH(NOLOCK)
        On ab.ContactId=y.ContactId

        TRUNCATE TABLE Temp_ContactBase_sub
       
        INSERT INTO  Temp_ContactBase_sub(ContactId)
        SELECT TOP 10000 ContactId FROM Temp_ContactBase_main WITH(NOLOCK)
           
END

INSERT INTO CRM_Staging_Data.dbo.CRM_Purge_Log(Script_Name,Purge_Date,Purge_user,[Message])
SELECT 'SP: ES_Purge_ContactBase',GETDATE(),USER_NAME(),@message

    IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_main') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
    BEGIN
        DROP TABLE Temp_ContactBase_main
    END
   
    IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_sub') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
    BEGIN
        DROP TABLE Temp_ContactBase_sub   
    END
       
END TRY  
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
   
    SELECT  @ErrorMessage = N'Error in SP: dbo.ES_Purge_ContactBase: ' + ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();
    INSERT INTO CRM_Staging_Data.dbo.CRM_Purge_Log(Script_Name,Purge_Date,Purge_user,[Message])
    SELECT 'SP: ES_Purge_ContactBase',GETDATE(),USER_NAME(),@message+@ErrorMessage
    RAISERROR( @ErrorMessage,@ErrorSeverity,@ErrorState );
END CATCH
END


 
 

Remove Leading and Trailing Zeros

DECLARE @BankAccount TABLE (AccNo VARCHAR(15))

INSERT @BankAccount SELECT '01010'

INSERT @BankAccount SELECT '0010200'

INSERT @BankAccount SELECT '000103000'

  

SELECT * FROM @BankAccount

  

--Methods to remove leading zeros

  

-- 1.)  converting to integer data type

SELECT CONVERT(INT,AccNo) AccNo FROM @BankAccount

-- NN - note, this method will only work if the data are clean

  

-- 2.)  using SUBSTRING 

  

SELECT SUBSTRING(AccNo,PATINDEX('%[^0]%',AccNo),LEN(AccNo)) AccNo FROM @BankAccount

  

-- 3.)  using REPLACE,LTRIM & RTRIM

  

SELECT REPLACE(LTRIM(REPLACE(AccNo,'0',' ')),' ','0') AccNo FROM @BankAccount

--To remove both leading & trailing zeros

  

SELECT REPLACE(RTRIM(LTRIM(REPLACE(AccNo,'0',' '))),' ','0') AccNo FROM @BankAccount

Reverse a string without using T-SQL REVERSE() function

DECLARE @StringToReverse VARCHAR(55)

SET @StringToReverse = 'Reverse a string with out using REVERSE() function'

 

;WITH cte AS (

      SELECT @StringToReverse AS string, CAST('' AS VARCHAR(55)) AS revStr, LEN(@StringToReverse) AS ln

      UNION ALL

      SELECT SUBSTRING(string,0,ln) AS string, CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55)) AS revStr, ln-1 AS ln

      FROM cte

      WHERE ln >= 1)

SELECT @StringToReverse AS String, revStr

FROM cte

WHERE ln = 0

SQL SERVER – Generating Row Number Without Ordering Any Columns

Row_number function is used to generate a serial number for a given record set. But you need to always use ORDER BY clause so that the numbers are assigned to the specific order.

Let us create the following dataset

CREATE TABLE #TEST (NAMES VARCHAR(100))
INSERT INTO #TEST
SELECT 'PINAL' UNION ALL
SELECT 'MAHESH' UNION ALL
SELECT 'SUNIL' UNION ALL
SELECT 'ARVIND' UNION ALL
SELECT 'MURUGAN'




Suppose you want to generate row number, you can use the following statement

SELECT *,ROW_NUMBER() OVER (ORDER BY NAMES) AS SNO FROM #TEST

The reasult is

The numbers are assigned based on ascending order of name

But what if you want to generate row numbers in the same order the data are added.

Can you omit the ORDER BY Clause?

SELECT *,ROW_NUMBER() OVER () AS SNO FROM #TEST

The above throws the following error

Msg 4112, Level 15, State 1, Line 1
The function ‘ROW_NUMBER’ must have an OVER clause with ORDER BY.

But there is a way. Just do not ORDER BY any columns, but ORDER BY a literal value as shown below

SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS SNO FROM #TEST

The result is

In place of SELECT 100, you can use anything like SELECT 1, SELECT ‘A’, SELECT NULL, etc

HOW TO INSERT UNIQUELY IDENTIFIED COLUMNS DATA INTO ANOTHER TABLE

SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM CRM_Staging_Data.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Data_Conversion_History')
    CREATE TABLE CRM_Staging_Data.dbo.Data_Conversion_History(
        [data_conversion_history_id] [int] IDENTITY(1,1) NOT NULL,
        [script_name] [nvarchar](1024) NULL,
        [run_date] [datetime] NULL
    ) 

IF EXISTS ( SELECT * FROM CRM_Staging_Data.dbo.Data_Conversion_History WITH(NOLOCK) WHERE script_name = '01_15-CLM-002_Data_Script' )
        RETURN;
BEGIN TRY
DECLARE @min_rowid UNIQUEIDENTIFIER
DECLARE @max_rowid UNIQUEIDENTIFIER
DECLARE @last_eas_contact_policy_relationshipBase_copy_log_id INT

SELECT 
    @max_rowid = MAX(eas_policycontactId) 
    ,@min_rowid = MIN(eas_policycontactId) 
FROM 
    dbo.eas_policycontactBase WITH(NOLOCK)

IF EXISTS (SELECT * FROM CRM_Staging_Data.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='eas_contact_policy_relationshipBase_copy_log')         
BEGIN      
    DROP TABLE CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log        
END

CREATE TABLE CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log        
    (        
        eas_contact_policy_relationshipBase_copy_log_id BIGINT IDENTITY(1,1), 
        eas_policycontactId  UNIQUEIDENTIFIER,
        CONSTRAINT PK_SRC_Policy_relationbase PRIMARY KEY CLUSTERED (eas_contact_policy_relationshipBase_copy_log_id)        
    )  

--Insert the first record. Others will use the while block
MERGE dbo.eas_contact_policy_relationshipBase AS target
USING (
        SELECT
            NEWID() AS eas_contact_policy_relationshipId
            ,eas_ContactId
            ,eas_PolicyNumberId
            ,eas_policycontactId
        FROM
            dbo.eas_PolicyContactBase WITH(NOLOCK)
        WHERE
            eas_policycontactId = @min_rowid
        AND     eas_ContactId IS NOT NULL 
        ) AS source
ON ( 1=0 )
WHEN NOT MATCHED THEN
INSERT VALUES (    source.eas_contact_policy_relationshipId,NULL,source.eas_ContactId,source.eas_PolicyNumberId )
OUTPUT
    source.eas_policycontactId
INTO
    CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log ( eas_policycontactId );

WHILE ( @min_rowid <= @max_rowid )
BEGIN
    ;WITH [UpdateList] AS (
        SELECT TOP (100000) 
            sel.eas_policycontactId
        FROM
            dbo.eas_PolicyContactBase sel WITH(NOLOCK) 
        WHERE
            sel.eas_policycontactId > @min_rowid
        AND     sel.eas_ContactId IS NOT NULL 
        ORDER BY
            sel.eas_policycontactId ASC
    )
    MERGE dbo.eas_contact_policy_relationshipBase AS target
    USING (
        SELECT
            NEWID() AS eas_contact_policy_relationshipId
            ,eas_ContactId
            ,eas_PolicyNumberId
            ,main.eas_policycontactId
        FROM
            dbo.eas_PolicyContactBase main  WITH(NOLOCK)
        INNER JOIN
            [UpdateList] list
            ON ( main.eas_policycontactId = list.eas_policycontactId )
        ) AS source
    ON ( 1=0 )
    WHEN NOT MATCHED THEN
    INSERT VALUES (    source.eas_contact_policy_relationshipId,NULL,source.eas_ContactId,source.eas_PolicyNumberId )
    OUTPUT
        source.eas_policycontactId
    INTO
        CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log ( eas_policycontactId );
       
   
    SELECT @last_eas_contact_policy_relationshipBase_copy_log_id = IDENT_CURRENT('CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log')
       
    SET @min_rowid = ( SELECT eas_policycontactId FROM CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log WITH(NOLOCK) 
                        WHERE eas_contact_policy_relationshipBase_copy_log_id = @last_eas_contact_policy_relationshipBase_copy_log_id )
       
    IF ( @min_rowid = @max_rowid )
        BREAK;
END

INSERT INTO CRM_Staging_Data.dbo.Data_Conversion_history (script_name,run_date)
VALUES('01_15-CLM-002_Data_Script',GETDATE())   

IF EXISTS (SELECT * FROM CRM_Staging_Data.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='eas_contact_policy_relationshipBase_copy_log')         
BEGIN      
    DROP TABLE CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log        
END

END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    SELECT 
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();

    RAISERROR( @ErrorMessage,@ErrorSeverity,@ErrorState );
END CATCH

UPDATE XML COLUMN DATA

DECLARE @cnt INT 
DECLARE @trace_log_id INT 
DECLARE @message_content NVARCHAR(MAX) 
DECLARE @temp TABLE(Id BIGINT IDENTITY(1,1),trace_log_id int,message_content NVARCHAR(MAX))

INSERT INTO @temp(trace_log_id,message_content)
SELECT trace_log_id,CAST(message_content as NVARCHAR(MAX)) FROM DBR_214438
--select * from @temp
select @cnt=COUNT(*) from @temp
--select @cnt

WHILE(@cnt>0)
BEGIN
      SELECT @trace_log_id=trace_log_id,@message_content=message_content FROM @temp where id=@cnt

      UPDATE @temp
      SET  message_content=REPLACE(message_content,'Monica','******')                                               
      FROM @temp 
      WHERE  trace_log_id=@trace_log_id AND id=@cnt
      UPDATE @temp
      SET  message_content=REPLACE(message_content,'Hermanski','*****')                                           
      FROM @temp 
      WHERE  trace_log_id=@trace_log_id AND id=@cnt

       UPDATE @temp
      SET  message_content=REPLACE(message_content,'1979-11-01T00:00:00','**********')                                            
      FROM @temp 
      WHERE  trace_log_id=@trace_log_id AND id=@cnt
      
                         
      UPDATE t1
      SET t1.message_content=CAST(t2.message_content as XML)
      FROM DBR_214438 t1
      join 
          @temp t2
          ON t1.trace_log_id=@trace_log_id
          AND t2.id=@cnt
     

      SET @cnt=@cnt-1
      --IF(@cnt=0)
      --BREAK;

END 


--drop table  DBR_214438
--select * into DBR_214438 from babji

select * from DBR_214438
--select * from babji