CODEWITHSUNDEEP
sqlsql-servert-sqlset-differencenotin
James123
James123

Reputation: 11672

Not in In SQL statement?

I have set of ids in excel around 5000 and in the table I have ids around 30000. If I use 'In' condition in SQL statment I am getting around 4300 ids from what ever I have ids in Excel. But If I use 'Not In' with Excel id. I have getting around 25000+ records. I just to find out I am missing with Excel ids in the table.

How to write sql for this?

Example: Excel Ids are

 1,
 2,
 3,
 4,
 5,
 6,
 7,
 8,
 9,
10,

Table has IDs

 1,
 2,
 3,
 4,
 6,
 8,
 9,
11,
12,
14,
15

Now I want get 5,7,10 values from Excel which missing the table?

Update:

What I am doing is 

SELECT  [GLID]      
  FROM [tbl_Detail] 
  where datasource = 'China' and  ap_ID  not in (5206896,
5206897,
5206898,
5206899,
5117083,
5143565,
5173361,
5179096,
5179097,
5179150)

Upvotes: 7

Views: 43641

Answers (4)

Vince Pergolizzi
Vince Pergolizzi

Reputation: 6614

Import your excel file into SQL Server using the Import Data Wizard found in SQL Server Management Studio.

Then you can write the following query to find any IDs which are in the file but not in the table:

SELECT id     
FROM imported_table
WHERE id NOT IN (SELECT id FROM db_table)

Upvotes: 3

Leniel Maccaferri
Leniel Maccaferri

Reputation: 102448

Try this:

SELECT    tableExcel.ID
FROM      tableExcel
WHERE     tableExcel.ID NOT IN(SELECT anotherTable.ID FROM anotherTable)

Here's an SQL Fiddle to try this: sqlfiddle.com/#!6/31af5/14

Upvotes: 8

JotaBe
JotaBe

Reputation: 39065

You should move excel data to a table in SQL Server, and then do the query in SQL Server.

  select distinct id from Excel where id not in (select your ids from Sqltable)

(Obviously select your ids from Sqltable is a select which returns the Ids existing on SQL Server).

You may think that moving data to SQL Server is hard to do, but, on the contrary, it's very easy:

1) create a table

  CREATE TABLE ExcelIds (Id int)

2) add a new column in excel with the following formula:

  ="insert into ExcelIds values(" & XX & ")"

where XX is the reference to the cell in the column with excel Ids.

3) copy the "inserts" from Excel into SSMS or whatever tool you're usin in SQL Server, and execute them.

Now you have 2 tables in SQL Server, so that querying it is absolutely easy.

When you're over, just drop the table

DROP TABLE ExcelIds

NOTE: I didn't create a key on SQL Server table because I suppose that the Ids can be repeated. Neither is justified to create a more complex SQL Query to avoid duplicates in ExcelIds for this ad hoc solution.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460340

You're probably looking for EXCEPT:

SELECT Value 
FROM @Excel 
EXCEPT
SELECT Value 
FROM @Table;

Edit:

Except will

  • treat NULL differently(NULL values are matching)
  • apply DISTINCT

unlike NOT IN

Here's your sample data:

declare @Excel Table(Value int);
INSERT INTO @Excel VALUES(1);
INSERT INTO @Excel VALUES(2);
INSERT INTO @Excel VALUES(3);
INSERT INTO @Excel VALUES(4);
INSERT INTO @Excel VALUES(5);
INSERT INTO @Excel VALUES(6);
INSERT INTO @Excel VALUES(7);
INSERT INTO @Excel VALUES(8);
INSERT INTO @Excel VALUES(9);
INSERT INTO @Excel VALUES(10);

declare @Table Table(Value int);
INSERT INTO @Table VALUES(1);
INSERT INTO @Table VALUES(2);
INSERT INTO @Table VALUES(3);
INSERT INTO @Table VALUES(4);
INSERT INTO @Table VALUES(6);
INSERT INTO @Table VALUES(8);
INSERT INTO @Table VALUES(9);
INSERT INTO @Table VALUES(11);
INSERT INTO @Table VALUES(12);
INSERT INTO @Table VALUES(14);
INSERT INTO @Table VALUES(15);

Upvotes: 5

Related Questions