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 (
  2. @Input NVARCHAR(MAX)
  3. ) RETURNS NVARCHAR(MAX) AS BEGIN
  4. DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '')
  5. SET @RET = REPLACE(@RET, CHAR(13), ' ')
  6. SET @RET = REPLACE(@RET, CHAR(10), ' ')
  7. SET @RET = REPLACE(@RET, CHAR(9), ' ')
  8. SET @RET = REPLACE(@RET, '''', ' ')
  9. SET @RET = REPLACE(@RET, ',', ' ')

  11. WHILE(CHARINDEX('  ', @RET) != 0) BEGIN
  12. SET @RET = REPLACE(@RET, '  ', ' ')
  13. END

  15. SET @Ret = TRIM(@RET)

  17. RETURN @RET
  18. END
  19. GO

  21. SELECT
  22.     '' as 'Keep? (Y)',
  23.     V1.Id, 
  24.     dbo.SafeSingleLine(V1.Final_ReferenceCode) as 'Final_ReferenceCode', 
  25.     dbo.SafeSingleLine(V1.Final_FullName) as 'Final_FullName', 
  26.     V1.Final_Status,
  27.     Script = CONCAT(''
  28.       --IF cell A### = "Y"
  29.       , '=IF(INDIRECT("A"&ROW()) = "Y",'
  30.   , '"'
  31.   , ' UPDATE'
  32.   , '   __M_Contacts'
  33.   , ' SET'
  34.   , '   Final_Status = Original_Status '
  35.   , ' WHERE 1=1'
  36.   , '   AND Id = '
  37.   , '     ''" & '
  38.   , '       INDIRECT("B"&ROW()) '
  39.   , '     & "'''
  40.   , '"'
  41.   --/THEN
  42.       , ', '
  43.   --ELSE
  44.   , '"'
  45.   , ' '
  46.   , '"'
  47.   --/ELSE
  48.       , ')'
  49.   )
  50. FROM
  51.     __M_Contacts V1
  52. ORDER BY
  53.     V1.Final_ReferenceCode, V1.Final_FullName

    • Related Articles

    • 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. ...

    • SQL Spreadsheets: Manually Prioritizing Matters

      --The following SQL will generate a list of matters and allow their priorities to easily be specified. CREATE OR ALTER FUNCTION SafeSingleLine ( @Input NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '') ...