February 14, 2006 at 1:13 pm
I want to insert only records found in my list and not found in my DR table. Currently though it inserts all companies and the checks don't work below in my WHERE clause:
INSERT INTO DR
SELECT m.customer,
c.name,
c.customer,
'',
0,
m.Branch,
0,
'',
'',
'',
0,
'',
'',
0,
0,
0,
0,
'IU' AS Type,
1 AS Active,
m.number,
0,
0,
0,
0,
0,
0,
'',
0,
0,
'',
'',
(SELECT TotalPostingDays from TotalPostingDays),
(SELECT CurrentPostingDAy from CurrentPostingDay)
FROM dbo.Master m (NOLOCK)
INNER JOIN dbo.Customer c ON c.Customer = m.Customer
WHERE c.customer NOT IN (select customernumber from DR group by customernumber)
AND c.customer IN (
'0000093',
'0000066',
'0000050',
'0000114',
'0000112',
'0000124',
'0000113',
'0000094',
'0000104',
'0000122',
'0000123',
'0000127',
'0000057',
'0000132',
'0000138',
'0000128',
'0000142',
'0000149',
'0000147',
'0000144',
'0000148',
'0000145',
'0000103',
'0000105',
'0000109',
'0000135',
'0000155',
'0000156',
'0000157',
'0000159',
'0000160',
'0000161',
'0000118',
'0000143',
'0000146',
'0000153',
'0000152',
'0000108',
'0000158',
'0000133')
AND c.customer <> '9999999'
February 14, 2006 at 1:18 pm
Try removing the blank line betwene your JOIN and WHERE so that SQL knows where the batch ends ?
February 14, 2006 at 1:34 pm
INSERT INTO DR
Can you continue with columns list for the table?
_____________
Code for TallyGenerator
February 14, 2006 at 1:53 pm
Whitespace shouldn't matter in a sql query.
also, my query runs the where clause, just that it's picking up all accounts. I want it to insert only those customers who are not in my DR table AND customers who are in my list of accounts
February 14, 2006 at 1:59 pm
You should use LEFT OUTER JOINS for this purpose
An example of the query is:
INSERT INTO DR
SELECT m.customer,
...
FROM dbo.Master m (NOLOCK)
INNER JOIN dbo.Customer c ON c.Customer = m.Customer
AND c.customer IN (
'0000093',
'0000066',
--rest of list
'0000133')
LEFT OUTER JOIN DR ON c.customer = DR.customernumber
--THIS IS REDUNDANT BECAUSE 9999999 IS NOT IN THE LIST
AND c.customer <> '9999999'
WHERE DR.customernumber IS NULL
Generally, 'NOT IN' performs very poorly.
Also, instead of,
(SELECT TotalPostingDays from TotalPostingDays),
(SELECT CurrentPostingDAy from CurrentPostingDay)
I find it the query performs better if you use some local variables because the way you did it, the select gets executed once for each row in the result. For example
declare @TotalPostingDays int
SELECT @TotalPostingDays = TotalPostingDays from TotalPostingDays
SELECT
--Rest of columns in select
@TotalPostingDays
Or, you could include the selects in the FROM clause with something like
INNER JOIN TotalPostingDays ON 1 = 1
February 14, 2006 at 2:17 pm
jeff, but I don't want a left join...I don't want ALL customers, only the ones who are not in the DR table AND then in my list...all others should not show if they are
a) not in the DR table and
b) not in my list
Wouldn't your left join include all customers, even if no match is found?
February 14, 2006 at 2:23 pm
By adding 'WHERE DR.CUSTOMERNUMBER IS NULL' to the select it will only return rows not in the DR table because for rows that are in the DR table and in the customer table, DR.CustomerNumber will not be null
Also, the LEFT OUTER JOIN is on the DR table not the customer table. This says for each customer include it in the set even if a DR row does not exist. By qualifying the WHERE clause, only NULL DR rows will be left in the set.
February 14, 2006 at 2:28 pm
The following SQL returns 'BBBBBB' and 'CCCCCC', the customers that are in the customer table but not in the DR table.
declare @master Table (customer varchar(20))
declare @Customer Table (customer varchar(20))
declare @DR table (customerNumber varchar(20))
insert @master values ('AAAAAA')
insert @master values ('BBBBBB')
insert @master values ('CCCCCC')
insert @Customer values ('AAAAAA')
insert @Customer values ('BBBBBB')
insert @Customer values ('CCCCCC')
insert @DR values ('AAAAAA')
select *
from @master m
inner join @Customer c on m.customer = c.customer
left outer join @DR dr on c.customer = dr.customerNumber
where dr.CustomerNumber IS NULL
February 14, 2006 at 2:31 pm
Maybe it's time to post some DDL for the tables involved.
There are 3 tables, each with a customer or customernumber column. Are they the same datatypes ?
February 14, 2006 at 2:36 pm
This below brings back no results which is not possible, it should be brining back some accounts. I also tried a top 1 because I basically just want one instance of the account so I can fill the insert with that account's name, ID, and the rest zeros for a report of mine.
SELECT top 1 m.customer,
c.name,
c.customer,
'',
0,
m.Branch,
0,
'',
'',
'',
0,
'',
'',
0,
0,
0,
0,
'UI' AS Type,
1 AS Active,
m.number,
0,
0,
0,
0,
0,
0,
'',
0,
0,
'',
'',
(SELECT TotalPostingDays from TotalPostingDays),
(SELECT CurrentPostingDAy from CurrentPostingDay)
FROM dbo.Master m (NOLOCK)
INNER JOIN dbo.Customer c ON c.Customer = m.Customer
AND c.customer IN ( '0000093',
'0000066',
'0000050',
'0000114',
'0000112',
'0000124',
'0000113'
'0000094',
'0000104',
'0000122',
'0000123',
'0000127',
'0000057',
'0000132',
'0000138',
'0000128',
'0000142',
'0000149',
'0000147',
'0000144',
'0000148',
'0000145',
'0000103',
'0000105',
'0000109',
'0000135',
'0000155',
'0000156',
'0000157',
'0000159',
'0000160',
'0000161',
'0000118',
'0000143',
'0000146',
'0000153',
'0000152',
'0000108',
'0000158',
'0000133')
LEFT OUTER JOIN DR ON c.customer = DR.customernumber
WHERE DR.customernumber IS NULL
February 14, 2006 at 2:47 pm
The problem is with your data. What does this return?:
select count(*) from master m inner join customer c on m.customer = c.customer
How about this?
select count(*) from master m inner join customer c on m.customer = c.customer and c.customer in (.....)
Using the following query, the list of known customers is 'AAAAA' and 'BBBBBB'. Only 'BBBBBB' is returned because it is not in DR but is in the list. 'AAAAA' is not returned because it is already in DR and 'CCCCCC' is not returned because it is not in the list.
declare @master Table (customer varchar(20))
declare @Customer Table (customer varchar(20))
declare @DR table (customerNumber varchar(20))
insert @master values ('AAAAAA')
insert @master values ('BBBBBB')
insert @master values ('CCCCCC')
insert @Customer values ('AAAAAA')
insert @Customer values ('BBBBBB')
insert @Customer values ('CCCCCC')
insert @DR values ('AAAAAA')
select *
from @master m
inner join @Customer c on m.customer = c.customer
AND c.customer IN ( 'AAAAAA', 'BBBBBB')
left outer join @DR dr on c.customer = dr.customerNumber
where dr.CustomerNumber IS NULL
February 14, 2006 at 3:22 pm
Once again:
INSERT INTO DR ( <columns list> )
Can you provide complete INSERT statement?
_____________
Code for TallyGenerator
February 14, 2006 at 4:06 pm
Tables have the same data types
February 14, 2006 at 4:33 pm
How about you run this query and post the results.
It builds a derived table of all the Customer Numbers you're including, then it takes that set and LEFT JOINs it to your other 3 tables, setting Yes or No flags on whether the CustomerNumber exists in each of those 3 tables. I don't think we're going to get any further with this thread until we see what this query returns:
Select dt.Customer,
Case When m.Customer Is Null Then 'N' Else 'Y' As InMaster,
Case When c.Customer Is Null Then 'N' Else 'Y' As InCustomer,
Case When d.CustomerNumber Is Null Then 'N' Else 'Y' As InDR
From
(
Select '0000093' As Customer Union All
Select '0000066' Union All
Select '0000050' Union All
Select '0000114' Union All
Select '0000112' Union All
Select '0000124' Union All
Select '0000113' Union All
Select '0000094' Union All
Select '0000104' Union All
Select '0000122' Union All
Select '0000123' Union All
Select '0000127' Union All
Select '0000057' Union All
Select '0000132' Union All
Select '0000138' Union All
Select '0000128' Union All
Select '0000142' Union All
Select '0000149' Union All
Select '0000147' Union All
Select '0000144' Union All
Select '0000148' Union All
Select '0000145' Union All
Select '0000103' Union All
Select '0000105' Union All
Select '0000109' Union All
Select '0000135' Union All
Select '0000155' Union All
Select '0000156' Union All
Select '0000157' Union All
Select '0000159' Union All
Select '0000160' Union All
Select '0000161' Union All
Select '0000118' Union All
Select '0000143' Union All
Select '0000146' Union All
Select '0000153' Union All
Select '0000152' Union All
Select '0000108' Union All
Select '0000158' Union All
Select '0000133'
) dt
Left Join dbo.Master As m On m.Customer = dt.Customer
Left Join dbo.Customer As c On c.Customer = dt.Customer
Left Join DR As d On d.CustomerNumber = dt.Customer
February 14, 2006 at 4:35 pm
Third time:
Can you provide complete INSERT statement?
INSERT
INTO DR ( <columns list> )
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply