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, '')
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