Solved

SQL Server 2005 'NOT IN' clause probelm

Posted on 2008-06-24
9
640 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

820 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