0

After some time I really don't know what is going wrong in here. (SQL is not my cup of tea I would say, but still need to figure this out hehe)

I have 2 tables, in different schema, that I need to pass the information from the "primary" into the "secondaries" if the "secondaries" don't have that line, so that's why I'm trying to use the not exist.

this is the query that I'm using:

--Select all from the primary
select 
    *
from 
    (select 
         *
     from 
         [server\sqlexpress].xptoPrimary.dbo.CcArtigos 
     where 
         Tipo_Doc in ('INE','INS') 
         and Armazem=2 
         and data_mov >='01-07-2018') as tprimary
where not exists
      --That not exist in the secondary
      (select * 
       from 
           [server\sqlexpress].xptoSecondary.dbo.CcArtigos as tsecondary
       where 
           tprimary.cod_art = tsecondary.cod_art 
           and Tipo_Doc in ('INE','INS') 
           and Armazem=2 
           and data_mov >='01-07-2018')

So from what I did understood from what I did find online (https://www.tutorialgateway.org/sql-not-exists-operator/), I'm trying to select all from the xptoPrimary that doesn't exist on the xptoSecondary BUT it returns 0 rows and acording to the site, don't see what is the problem with the query, the logic from the site:

SELECT [Column Names]
FROM [Source]
WHERE NOT EXISTS (Write Subquery to Check)

The test db's the primary have 91 lines and the secondary has 90 lines so I should get the row from the primary that the secondary doesn't have but I get 0 rows :/

Edit:

select *
from [server\sqlexpress].xptoPrimary.dbo.CcArtigos
where not exists 
  (select * from [server\sqlexpress].xptoSecondary.dbo.CcArtigos as tsecondary)

Edit 2

select * from [server\sqlexpress].xptoPrimary.dbo.CcArtigos AS tPrimary
WHERE NOT EXISTS (SELECT 
                      * 
                  FROM 
                      [server\sqlexpress].xptoSecondary.dbo.CcArtigos as tSecondary
                  WHERE 
                      tPrimary.cod_art = tSecondary.cod_art 
                      AND tPrimary.Tipo_Doc = tSecondary.Tipo_Doc
                      AND tPrimary.Armazem = tSecondary.Armazem
                      AND tPrimary.data_mov = tSecondary.data_mov);
3
  • If both DB are in the same server you can remove [server\sqlexpress]. Inside NOT EXISTS query: Does Tipo_Doc, Armazem and data_mov require a table prefix?
    – McNets
    Jun 26, 2019 at 14:31
  • On the test server, is on the same, on the client is not. if I remove the where (check edit) it still returns 0 rows
    – Camadas
    Jun 26, 2019 at 14:35
  • data_mov >='01-07-2018' looks utterly suspicious. Apr 27, 2021 at 9:33

1 Answer 1

1

I don't know table schema of both tables, but assuming they have the same schema you can do something like this:

SELECT
    *
FROM 
    xptoPrimary.dbo.CcArtigos AS tPrimary
WHERE
    Tipo_Doc in ('INE','INS') 
    AND Armazem=2 
    AND data_mov >='01-07-2018'
WHERE NOT EXISTS (SELECT 
                      1 
                  FROM 
                      xptoSecondary.dbo.CcArtigos as tSecondary
                  WHERE 
                      tPrimary.cod_art = tSecondary.cod_art 
                      AND tPrimary.Tipo_Doc = tSecondary.Tipo_Doc
                      AND tPrimary.Armazem = tSecondary.Armazem
                      AND tPrimary.data_mov = tSecondary.data_mov);

That is, for each row of tPrimary table, there isn't a row in tSecondary that match the same criteria.

1
  • Getting sql syntax error near where, but after changing a little only to (check update 2) I will get the missing row on slave that the master has
    – Camadas
    Jun 26, 2019 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.