There is an import.xls file with format data in it:

+------+--------+ | id | num | +------+--------+ | 1 | 0A3478 | | 2 | 0A3479 | | 3 | 0A3480 | | 4 | 0B1267 | | 5 | 0C3498 | +------+--------+

I import into sql by:

 SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\Folder\import.xlsx; Extended Properties=Excel 12.0 Xml')...[sheet$]

There is a table where further you need to insert these values:

 CREATE TABLE TABLES( ID int NOT NULL, NUM int NOT NULL )

It has data, let's say it:

 +------+--------+ | id | num | +------+--------+ | 1 | 0A3478 | | 2 | 0A3479 | | 80 | 9X0298 | | 90 | 9X6941 | | 100 | 0W5963 | +------+--------+

And if you do an xls import first into a table, for example #tmp, and make a query of the form:

 SELECT * FROM #tmp WHERE num not in (SELECT num FROM TABLES)

That request does not return anything, but should in theory:

 +------+--------+ | id | num | +------+--------+ | 3 | 0A3480 | | 4 | 0B1267 | | 5 | 0C3498 | +------+--------+

I did the unloading of xls hundreds of times, and then everything is simple to the banal, but something doesn’t work, and let's say “zamylilas”, you need a fresh look, tell me who’s wrong here))

  • And you checked #tmp contents? maybe there num is NULL. - Mike
  • Yes, there are exactly all the lines that were in the file .. - noanother
  • Then this is some kind of mystery. It only comes to mind that in the TABLES table the column is called non-num and the subquery to not in under num means this column from #tmp. Try explicitly specifying TABLES.num to eliminate such a misunderstanding. - Mike
  • Yes, this is some kind of incomprehensible tin) And the most interesting thing is that if you do NOT NOT IN, but vice versa IN, then the first 2 lines are returned, with id 1 and 2..zhest - noanother
  • one
    Check the table TABLES for an entry where num = NULL. If so, add SELECT * FROM #tmp WHERE num not in query (SELECT num FROM TABLES WHERE num IS NOT NULL) - koshe

0