SQL Spreadsheets: Cherry Picking Contacts

SQL Spreadsheets: Cherry Picking Contacts

The following SQL command will generate a spreadsheet that can be used to cherry-pick specific contacts.

  1. CREATE OR ALTER FUNCTION SafeSingleLine (
    @Input NVARCHAR(MAX)
    ) RETURNS NVARCHAR(MAX) AS BEGIN
    DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '')

    SET @RET = REPLACE(@RET, CHAR(13), ' ')
    SET @RET = REPLACE(@RET, CHAR(10), ' ')
    SET @RET = REPLACE(@RET, CHAR(9), ' ')
    SET @RET = REPLACE(@RET, '''', ' ')
    SET @RET = REPLACE(@RET, ',', ' ')

    WHILE(CHARINDEX(' ', @RET) != 0) BEGIN
    SET @RET = REPLACE(@RET, ' ', ' ')
    END

    SET @Ret = TRIM(@RET)

    RETURN @RET
    END
    GO

    CREATE OR ALTER FUNCTION ColumnReference(
    @Input NVARCHAR(MAX)
    ) RETURNS NVARCHAR(MAX) AS BEGIN
    DECLARE @RET NVARCHAR(MAX) = CONCAT(
    'INDIRECT("', @INPUT, '"&ROW())'
    )
    RETURN @RET
    END
    GO

    CREATE OR ALTER FUNCTION SqlEncode(
    @Input NVARCHAR(MAX)
    ) RETURNS NVARCHAR(MAX) AS BEGIN
    DECLARE @RET NVARCHAR(MAX) = CONCAT(
    'SUBSTITUTE(', @INPUT, ', "''", "''''")'
    )
    RETURN @RET
    END
    GO

    CREATE OR ALTER FUNCTION ColumnReferenceValue(
    @Input NVARCHAR(MAX)
    ) RETURNS NVARCHAR(MAX) AS BEGIN

    DECLARE @RawValue NVARCHAR(MAX) = dbo.ColumnReference(@Input)
    DECLARE @Encoded NVARCHAR(MAX) = dbo.SqlEncode(@RawValue)

    DECLARE @RET NVARCHAR(MAX) = CONCAT(
    '''" & ',
    @Encoded,
    '& "'''
    )
    RETURN @RET
    END
    GO

    SELECT
    /*A*/ '' as 'Keep? (Y)',
    /*B*/ V1.Id,
    /*C*/ dbo.SafeSingleLine(V1.Final_ReferenceCode) as 'Final_ReferenceCode',
    /*D*/ dbo.SafeSingleLine(V1.Final_Kind) as 'Final_Kind',
    /*E*/ dbo.SafeSingleLine(V1.Final_FullName) as 'Final_FullName',
    /*E*/ dbo.SafeSingleLine(V1.Final_FirstName) as 'Final_FirstName',
    /*E*/ dbo.SafeSingleLine(V1.Final_MiddleName) as 'Final_MiddleName',
    /*E*/ dbo.SafeSingleLine(V1.Final_LastName) as 'Final_LastName',
    /*G*/ dbo.SafeSingleLine(V1.Final_Status) as 'Final_Status',
    /*H*/ Script = CONCAT(''
    --IF cell A### = "Y"
    , '=IF(', dbo.ColumnReference('A'), '= "Y",'
    , '"'
    , ' UPDATE'
    , ' __M_Contacts'
    , ' SET Id=Id'
    /* Add or remove colums as desired */
    , ' , Final_ReferenceCode = ', dbo.ColumnReferenceValue('C')
    , ' , Final_Kind = ', dbo.ColumnReferenceValue('D')
    , ' , Final_FullName = ', dbo.ColumnReferenceValue('E')
    , ' , Final_FirstName = ', dbo.ColumnReferenceValue('F')
    , ' , Final_MiddleName = ', dbo.ColumnReferenceValue('G')
    , ' , Final_LastName = ', dbo.ColumnReferenceValue('H')
    , ' , Final_Status = ', dbo.ColumnReferenceValue('I')
    , ' WHERE 1=1'
    , ' AND Id = ', dbo.ColumnReferenceValue('B')
    , '"'
    --/THEN
    , ', '
    --ELSE
    , '"'
    , ' '
    , '"'
    --/ELSE
    , ')'
    )
    FROM
    __M_Contacts V1
    ORDER BY
    V1.Final_ReferenceCode,
    V1.Final_Kind,
    V1.Final_FullName,
    V1.Final_FirstName,
    V1.Final_MiddleName,
    V1.Final_LastName

    • Related Articles

    • SQL Spreadsheets: Cherry Picking Contacts

      The following SQL command will generate a spreadsheet that can be used to cherry-pick specific contacts. CREATE OR ALTER FUNCTION SafeSingleLine ( @Input NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '') ...
    • SQL Spreadsheets: Cherry-Picking Records

      As a general rule, we recommend that SQL scripts be used to mass-select/prune records; however, sometimes that is not possible. In these cases, we recommend having a member of the law firm complete a Google spreadsheet that clearly indicates what ...
    • SQL Spreadsheets: Cherry Picking Matters

      The following SQL command will generate a spreadsheet that can be used to cherry-pick specific matters. CREATE OR ALTER FUNCTION SafeSingleLine ( @Input NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '') ...
    • SQL Spreadsheets: Cherry-Picking Custom Fields

      The following SQL command will generate a Google Sheet that users can use to cherry-pick custom field definitions. SELECT '' as 'Keep? (Y)', V1.Id, V1.Final_Parent_Type, V1.Final_Kind, V1.Final_Subject, V1.Final_Description, V1.Final_Status, ...
    • SQL Spreadsheets: Reassigning Practice Areas

      Sometimes users want to reassign practice areas as part of the data restore into a new system. The following process makes it easy. Generate the SQL Spreadsheet Run the following command then copy the results and the headers into a Google Doc. ...