Solved

SQL Server 2005 'NOT IN' clause probelm

Posted on 2008-06-24
9
638 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
  • 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
 

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now