Insert a record into a temporary table called #Temporal, then I did a select and concatenate it since I need it that way, it looks like this:
SELECT
TipoInstitucion + '|' + CodigoInstitucion + '|' + FechaReporteDatos + '|' + Identidad + '|' +
NumeroTransaccion + '|' + TipoParticipacion
FROM #TEMPORAl
The result it shows me is this:
01|34|20210228|1808199400324|202102261202330628478808|1
What I need is to be able to insert another record exactly the same but with the difference that the end field instead of 1 says 3, that is, the result I need is this:
01|34|20210228|1808199400324|202102261202330628478808|1
01|34|20210228|1808199400324|202102261202330628478808|3
The code I am using for the insert is this:
DECLARE @FechaInicio DATE
DECLARE @FechaFinal DATE
SET @FechaInicio = '2021-02-01'
SET @FechaFinal = '2021-02-28'
SELECT
'01' AS [TipoInstitucion]
,'34' AS [CodigoInstitucion]
,FORMAT(@FechaFinal, 'yyyyMMdd') AS [FechaReporteDatos]
,COALESCE(c.TaxCode,co.IdentNum,SUBSTRING(co.IdentDocument, 0, CHARINDEX(';', co.IdentDocument))) AS [Identidad]
,CONCAT(FORMAT(bp.Created, 'yyyyMMddHHMMss'), '0', CONVERT(VARCHAR(15), bp.BPId)) AS [NumeroTransaccion]
,'1' AS [TipoParticipacion]
INTO #TEMPORAl
FROM dbo.BusinessProcesses AS bp WITH(NOLOCK)
LEFT JOIN dbo.BPClientsRelations AS bpr WITH(NOLOCK) ON bpr.BPId = bp.BPId
LEFT JOIN dbo.Clients AS c ON c.ClientId = bpr.ClientId
LEFT JOIN dbo.OuterClients AS co ON co.OuterClientId = bpr.OuterClientId
WHERE bp.BPId='628478808'
You can achieve this by altering your original query a bit, to include both types at once in the select, for example by creating a table on the fly in the from .
The easiest way to create the table with only 2 records is with a sin clause
values
or with the union of twoselect
sfrom
:Or, as I said before, you can change the table on the fly by: