Making temporary table comparison

Question

Making temporary table comparison

Navigation

#1 by (0 votes)

0

Good morning, I have questions on how to perform a comparison between a temporary table and a database table. What I want to return is, among all the data in 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]

sql sql-server

asked by anonymous 14.12.2018 / 13:47

1 answer

Change default Google map pin color Callback in JS with API call

score 0 · Accepted Answer

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

Rudolph, that'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;