• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

SQL Server 2005 'NOT IN' clause probelm

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
johnyue
Asked:
johnyue
  • 5
  • 2
  • 2
2 Solutions
 
dportasCommented:
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
 
chapmandewCommented:
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
 
johnyueAuthor Commented:
Let me try. It will take long to run because there are 138m records in my trans table
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
johnyueAuthor Commented:
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
 
johnyueAuthor Commented:
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
 
johnyueAuthor Commented:
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
 
chapmandewCommented:
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
 
johnyueAuthor Commented:
No, This is not the case.
0
 
dportasCommented:
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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now