Geek Answers Handbook

Not in an SQL statement?

I have a set of identifiers in excel about 5000, and in the table I have identifiers about 30000. If I use the "In" condition in SQL status, I get about 4300 identifiers from what has ever been in Excel. But if I use "Not In" with an Excel ID. I have about 25,000+ entries. I just found out that I am missing the Excel identifiers in the table.

How to write sql for this?

Example: Excel Identifiers

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

Table has identifiers

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

Now I want to get values 5,7,10from Excel that do not have a table?

Update:

What i do

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

You are probably looking for EXCEPT:

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

Edit :

EXCEPT will be

NOT IN

:

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);
+4

:

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

SQL : sqlfiddle.com/#!6/31af5/14

+7

Excel SQL Server , SQL Server Management Studio.

, ID, , :

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

excel SQL Server, SQL Server.

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

(, Sqltable - , , SQL Server).

, SQL Server , , , :

1)

  CREATE TABLE ExcelIds (Id int)

2) excel :

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

XX - excel.

3) "" Excel SSMS , SQL Server, .

2 SQL Server, .

,

DROP TABLE ExcelIds

. SQL Server, , . SQL-, ExcelIds .

0

More articles:


All Articles