CODEWITHSUNDEEP
sqlsql-servert-sql
Paco Meraz
Paco Meraz

Reputation: 497

Insert into tables values not contained in another

i'm trying to insert data into one table from another 

INSERT INTO test(CRED_NUMERO, GRP_NUMERO, ARE_NUMERO, CRACTI_NUMERO, CRAC_DIFPORGRUPO, 
JORN_NUMERO, TURN_NUMERO, CRAC_QTDTOTALACESSOS, CRAC_QTDACESSOS, CRAC_VERFSENHA, 
CRAC_VERFDIGITAL, CRAC_ULTPASSAGEM, CRAC_ULTPASSAGEMPONTO)
VALUES ((SELECT PES_NUMERO FROM PESSOAS WHERE PES_NUMERO NOT IN (SELECT CRED_NUMERO 
FROM CRED_ACESSO)),'1', '1', '1', NULL,NULL,NULL,'999', '0', '0', '0',
'2016-08-20 20:02:00.000','2000-01-01 00:00:00.000')

However when running the query the following error is shown

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

I have read previous questions regarding the same error and it is suggested to use "IN" instead of "=", however, the select query has a NOT IN. Could you please help me with this? Thanks in advance.

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Instead of insert . . . values, using insert . . select:

INSERT . . .
    SELECT PES_NUMERO, ,'1', '1', '1', NULL, NULL, NULL, '999', '0', '0', '0',
           '2016-08-20 20:02:00.000', '2000-01-01 00:00:00.000'
    FROM PESSOAS
    WHERE PES_NUMERO NOT IN (SELECT CRED_NUMERO FROM CRED_ACESSO);

Note: I strongly advise you to use NOT EXISTS rather than NOT IN. NOT IN returns no rows if CRED_NUMERO is ever NULL. The following is just a good habit, so you don't ever have this problem:

INSERT . . .
    SELECT pPES_NUMERO, ,'1', '1', '1', NULL, NULL, NULL, '999', '0', '0', '0',
           '2016-08-20 20:02:00.000', '2000-01-01 00:00:00.000'
    FROM PESSOAS p
    WHERE NOT EXISTS (SELECT 1 CRED_ACESSO ca WHERE p.PES_NUMERO = ca.CRED_NUMERO);

Upvotes: 4

Related Questions