Solved

SQL Server 2005 'NOT IN' clause probelm

Posted on 2008-06-24
9
642 Views
Last Modified: 2013-11-30
Dear Expert,

I have a query like this:
insert into dbo.Master_Tier_Assignment
Select 'Non-Named', 'Other', 'P', 500, T.Trimed_term, '199', 500,
Cast('2008-04-01T00:00:00.001' AS Datetime) , 0.07, 0, 3, 'General', 4 from Month_trans T where T.trimed_term not in
(Select distinct Term from dbo.Master_Assignment)

Both trimed_term and Term are nvarchar(8)

When I run this query it reurns nothing  the message was (0) row effected which is not right. BUt when I take 'NOT' out just leave the 'IN' clause this query gave me coorect answer.

Please help !

John
0
Comment
Question by:johnyue
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 22

Accepted Solution

by:
dportas earned 400 total points
ID: 21858778
Try the following. Nulls in the Term column would make a big difference.

INSERT INTO dbo.Master_Tier_Assignment
 SELECT 'Non-Named', 'Other', 'P', 500, T.Trimed_term, '199', 500,
  Cast('2008-04-01T00:00:00.001' AS Datetime) , 0.07, 0, 3, 'General', 4
 FROM Month_trans T WHERE T.trimed_term NOT IN
 (SELECT DISTINCT Term
  FROM dbo.Master_Assignment
  WHERE Term IS NOT NULL) ;
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 100 total points
ID: 21858806
Also, just as an FYI, you don't need to use DISTINCT when using a subquery, it automatically returns a distinct list for you.
0
 

Author Comment

by:johnyue
ID: 21858873
Let me try. It will take long to run because there are 138m records in my trans table
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:johnyue
ID: 21860861
Hi dportas and other expert,

I run this revised query, the result is still the same: (0) row effected. Any other ideas?

Thanks!

John
0
 

Author Comment

by:johnyue
ID: 21860984
I made a little change by add a distinct

INSERT INTO dbo.Master_Tier_Assignment
 SELECT DISTINCT 'Non-Named', 'Other', 'P', 500, T.Trimed_term, '199', 500,
  Cast('2008-04-01T00:00:00.001' AS Datetime) , 0.07, 0, 3, 'General', 4
 FROM Month_trans T WHERE T.trimed_term NOT IN
 (SELECT DISTINCT Term
  FROM dbo.Master_Assignment
  WHERE Term IS NOT NULL) ;
0
 

Author Comment

by:johnyue
ID: 21861452
Please help.  My query returns (0) row effected message.

Here is the query
NSERT INTO dbo.Master_Tier_Assignment
 SELECT DISTINCT 'Non-Named', 'Other', 'P', 500, T.Trimed_term, '199', 500,
  Cast('2008-04-01T00:00:00.001' AS Datetime) , 0.07, 0, 3, 'General', 4
 FROM Month_trans T WHERE T.trimed_term NOT IN
 (SELECT DISTINCT Term
  FROM dbo.Master_Assignment
  WHERE Term IS NOT NULL) ;
Both trimed_term and Term are nvarchar(8)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21861690
by chance, do all of the values in the trimmed_term field in your month_trans table also exist in your master_assignment table?
0
 

Author Comment

by:johnyue
ID: 21866011
No, This is not the case.
0
 
LVL 22

Expert Comment

by:dportas
ID: 21866470
Another possibility: Are you sure that "Term" is the name of a column in dbo.Master_Assignment only. If Term exists in dbo.Master_Tier_Assignment then you may get an error or no rows returned.

If you need more help then please post enough code to reproduce the problem: CREATE TABLE statements (simplified if you like) and at least one INSERT statement to create an example row.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question