Performing temporary table comparison

Question

Good morning, I have doubts about how to compare a temporary table with a database table. What I want to return is, among all the data of the temporary table it returns the ones I have in the database(null) and the ones I do not have in the database.

IF OBJECT_ID('TEMPDB.DBO.#TEMP') IS NOT NULL
DROP TABLE #TEMP
CREATE TABLE #TEMP (
CODIGO VARCHAR(100)
)
...
...
INSERT INTO #TEMP VALUES ('20023275000158')
INSERT INTO #TEMP VALUES ('20023275000158')

UPDATE #TEMP SET CODIGO = '00' + CODIGO WHERE LEN(CODIGO) = 13 -- adiciona 00 antes do número
UPDATE #TEMP SET CODIGO = '0' + CODIGO WHERE LEN(CODIGO) = 14

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT  DISTINCT    [cbs].[BodyShopId] [Oficina],
        [cbs].[BodyShopBusinessId] [CNPJ],
        [cbs].[AdditionalData].value('(/AdditionalDataForBodyShop/IsDiamond[1]', 'BIT' ) [OficinaDiamante],
        [t].[CODIGO]

FROM        #TEMP t
    LEFT JOIN [Country].[BodyShop] [cbs]
            ON [t].CODIGO = [cbs].[BodyShopBusinessId]

--WHERE [cbs].[AdditionalData].value('(/AdditionalDataForBodyShop/IsDiamond)[1]', 'BIT' ) = 1

order by [Oficina]

Answer 1

What I want to return is, among all the data of the temporary table it returns the ones I have in the bank and the ones I do not have in the bank

Rudolph, here’s a suggestion.

-- código #1 v2
with 
Unico_Temp as (
SELECT CODIGO
  from #TEMP
  where CODIGO is not null
),
Unico_BodyShop as (
SELECT distinct BodyShopBusinessId
  from Country.BodyShop
  where BodyShopBusinessId is not null
)
SELECT T1.CODIGO, 
       case when T2.BodyShopBusinessId is null
                 then 'Não está no banco' 
            else 'Está no banco' end as Observação
  from Unico_Temp as T1
       left join Unico_BodyShop as T2 on T1.CODIGO = T2.BodyShopBusinessId;