December 15, 2015 at 12:36 pm
The below query parses correctly, but I'm not getting any records to insert. I want to insert records into UO from UAO where UAO does not have any matching [InvNo]. There should be some 40,000 records that should insert. What am I doing wrong?
INSERT INTO UO
(FullName, Company, Attn, Address1, Address2, Address3, City, Statte, ZipCode, CountryID, Phone, UPSCustID, InvNo, UPSServiceType, ResInd, UPSBillTo, PONo,
ShipperNoFL, ShipperNoCA, ShipFromCompany, ShipFromAttn, ShipFromAdd1, ShipFromAdd2, ShipFromAdd3, ShipFromCity, ShipFromState, ShipFromZip, ShipFromCountry, PackageType,
TaxID, QVN, QVNEmail1, QVNOnShip1, QVNOnDelivery1, QVNEmail2, QVNOnShip2, QVNOnDelivery2, DelivConf, SignReq, Datte)
SELECT FullName, Company, Attn, Address1, Address2, Address3, City, Statte, ZipCode, CountryID, Phone, UPSCustID, InvNo, UPSServiceType, ResInd, UPSBillTo, PONo,
'388563' As ShipperNoFL, '589877' As ShipperNoCA, ShipFromCompany, ShipFromAttn, ShipFromAdd1, ShipFromAdd2, ShipFromAdd3, ShipFromCity, ShipFromState, ShipFromZip, ShipFromCountry,
PackageType, TaxID, QVN, QVNEmail1, QVNOnShip1, QVNOnDelivery1, QVNEmail2, QVNOnShip2, QVNOnDelivery2, DelivConf, SignReq, Datte
FROM UAO
WHERE NOT EXISTS
(
SELECT UAO.InvNo
FROM UAO INNER JOIN
UO ON UO.InvNo = UAO.InvNo
)
December 15, 2015 at 1:07 pm
I think you're missing a join.
SELECT FirstName
FROM NewRecs n
WHERE NOT EXISTS (SELECT FirstName
FROM ExistingRecs e
WHERE e.FirstName = n.FirstName);
Note the join between NewRecs and ExistingRecs in the Subselect.
it's the INNER JOIN in your subselect. That's lossy (non-matching records on either side of the join are eliminted, which is what you do NOT want.)
December 15, 2015 at 1:19 pm
Your exists subquery has no reference to the table in the outer query. Hence it will only be evaluated once and if the subquery returns any rows at all, the NOT EXISTS returns FALSE for all rows.
I suspect what you actually want is
INSERT INTO UO
(FullName, Company, Attn, Address1, Address2, Address3, City, Statte, ZipCode, CountryID, Phone, UPSCustID, InvNo, UPSServiceType, ResInd, UPSBillTo, PONo,
ShipperNoFL, ShipperNoCA, ShipFromCompany, ShipFromAttn, ShipFromAdd1, ShipFromAdd2, ShipFromAdd3, ShipFromCity, ShipFromState, ShipFromZip, ShipFromCountry, PackageType,
TaxID, QVN, QVNEmail1, QVNOnShip1, QVNOnDelivery1, QVNEmail2, QVNOnShip2, QVNOnDelivery2, DelivConf, SignReq, Datte)
SELECT FullName, Company, Attn, Address1, Address2, Address3, City, Statte, ZipCode, CountryID, Phone, UPSCustID, InvNo, UPSServiceType, ResInd, UPSBillTo, PONo,
'388563' As ShipperNoFL, '589877' As ShipperNoCA, ShipFromCompany, ShipFromAttn, ShipFromAdd1, ShipFromAdd2, ShipFromAdd3, ShipFromCity, ShipFromState, ShipFromZip, ShipFromCountry,
PackageType, TaxID, QVN, QVNEmail1, QVNOnShip1, QVNOnDelivery1, QVNEmail2, QVNOnShip2, QVNOnDelivery2, DelivConf, SignReq, Datte
FROM UAO
WHERE NOT EXISTS
(
SELECT 1
FROM UO
WHERE UO.InvNo = UAO.InvNo -- match to table in outer query
)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2015 at 1:22 pm
pietlinden (12/15/2015)
it's the INNER JOIN in your subselect. That's lossy (non-matching records on either side of the join are eliminted, which is what you do NOT want.)
It's not that the join is lossy, it's that it's there at all. The EXISTS needs to join (in WHERE) to the table in the outer query, not a second instance of the table in the subquery. As written it's essentially either WHERE NOT EXISTS (SELECT 1 WHERE 1=0) or WHERE NOT EXISTS (SELECT 1 WHERE 1=1). Because there's no join to the outer query, the NOT EXISTS either returns TRUE or FALSE for every single row in the outer resultset.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2015 at 1:45 pm
This worked for me, sort of. UAO.InvNo had a few records that were duped which stopped the query. When I deleted the dupes the query ran as expected. Thanks!
INSERT INTO UO
(FullName, Company, Attn, Address1, Address2, Address3, City, Statte, ZipCode, CountryID, Phone, UPSCustID, InvNo, UPSServiceType, ResInd, UPSBillTo, PONo,
ShipperNoFL, ShipperNoCA, ShipFromCompany, ShipFromAttn, ShipFromAdd1, ShipFromAdd2, ShipFromAdd3, ShipFromCity, ShipFromState, ShipFromZip, ShipFromCountry, PackageType,
TaxID, QVN, QVNEmail1, QVNOnShip1, QVNOnDelivery1, QVNEmail2, QVNOnShip2, QVNOnDelivery2, DelivConf, SignReq, Datte)
SELECT FullName, Company, Attn, Address1, Address2, Address3, City, Statte, ZipCode, CountryID, Phone, UPSCustID, InvNo, UPSServiceType, ResInd, UPSBillTo, PONo,
'388563' As ShipperNoFL, '589877' As ShipperNoCA, ShipFromCompany, ShipFromAttn, ShipFromAdd1, ShipFromAdd2, ShipFromAdd3, ShipFromCity, ShipFromState, ShipFromZip, ShipFromCountry,
PackageType, TaxID, QVN, QVNEmail1, QVNOnShip1, QVNOnDelivery1, QVNEmail2, QVNOnShip2, QVNOnDelivery2, DelivConf, SignReq, Datte
FROM UAO
WHERE NOT EXISTS
(
SELECT 1
FROM UO
WHERE UO.InvNo = UAO.InvNo -- match to table in outer query
)
December 17, 2015 at 12:42 pm
shank-130731 (12/15/2015)
This worked for me, sort of. UAO.InvNo had a few records that were duped which stopped the query. When I deleted the dupes the query ran as expected. Thanks!
INSERT INTO UO
(FullName, Company, Attn, Address1, Address2, Address3, City, Statte, ZipCode, CountryID, Phone, UPSCustID, InvNo, UPSServiceType, ResInd, UPSBillTo, PONo,
ShipperNoFL, ShipperNoCA, ShipFromCompany, ShipFromAttn, ShipFromAdd1, ShipFromAdd2, ShipFromAdd3, ShipFromCity, ShipFromState, ShipFromZip, ShipFromCountry, PackageType,
TaxID, QVN, QVNEmail1, QVNOnShip1, QVNOnDelivery1, QVNEmail2, QVNOnShip2, QVNOnDelivery2, DelivConf, SignReq, Datte)
SELECT FullName, Company, Attn, Address1, Address2, Address3, City, Statte, ZipCode, CountryID, Phone, UPSCustID, InvNo, UPSServiceType, ResInd, UPSBillTo, PONo,
'388563' As ShipperNoFL, '589877' As ShipperNoCA, ShipFromCompany, ShipFromAttn, ShipFromAdd1, ShipFromAdd2, ShipFromAdd3, ShipFromCity, ShipFromState, ShipFromZip, ShipFromCountry,
PackageType, TaxID, QVN, QVNEmail1, QVNOnShip1, QVNOnDelivery1, QVNEmail2, QVNOnShip2, QVNOnDelivery2, DelivConf, SignReq, Datte
FROM UAO
WHERE NOT EXISTS
(
SELECT 1
FROM UO
WHERE UO.InvNo = UAO.InvNo -- match to table in outer query
)
Yes, essentially you are picking only those records from your source( in this case UAO ) that are not already in the destination. I would suggest you stage the data and dedup it in place (UAO) before you attempt the above. It is all part of the data checks. Of course it is easier said than done. You have to figure why you are getting dupes, how to address that , and what version of InvNo is the correct one. It is not necessarily an easy task. Good luck.
----------------------------------------------------
December 17, 2015 at 12:44 pm
GilaMonster (12/15/2015)
Your exists subquery has no reference to the table in the outer query. Hence it will only be evaluated once and if the subquery returns any rows at all, the NOT EXISTS returns FALSE for all rows.I suspect what you actually want is
INSERT INTO UO(FullName, Company, Attn, Address1, Address2, Address3, City, Statte, ZipCode, CountryID, Phone, UPSCustID, InvNo, UPSServiceType, ResInd, UPSBillTo, PONo,
ShipperNoFL, ShipperNoCA, ShipFromCompany, ShipFromAttn, ShipFromAdd1, ShipFromAdd2, ShipFromAdd3, ShipFromCity, ShipFromState, ShipFromZip, ShipFromCountry, PackageType,
TaxID, QVN, QVNEmail1, QVNOnShip1, QVNOnDelivery1, QVNEmail2, QVNOnShip2, QVNOnDelivery2, DelivConf, SignReq, Datte)
SELECT FullName, Company, Attn, Address1, Address2, Address3, City, Statte, ZipCode, CountryID, Phone, UPSCustID, InvNo, UPSServiceType, ResInd, UPSBillTo, PONo,
'388563' As ShipperNoFL, '589877' As ShipperNoCA, ShipFromCompany, ShipFromAttn, ShipFromAdd1, ShipFromAdd2, ShipFromAdd3, ShipFromCity, ShipFromState, ShipFromZip, ShipFromCountry,
PackageType, TaxID, QVN, QVNEmail1, QVNOnShip1, QVNOnDelivery1, QVNEmail2, QVNOnShip2, QVNOnDelivery2, DelivConf, SignReq, Datte
FROM UAO
WHERE NOT EXISTS
(
SELECT 1
FROM UO
WHERE UO.InvNo = UAO.InvNo -- match to table in outer query
)
In
SELECT 1
FROM UO
WHERE UO.InvNo = UAO.InvNo -- match to table in outer query
Does the SELECT 1 provide a big performance gain or just prevents a call to the disk that may get delayed? I do this but I've not in my experience seen any ill effects from just returning a column from the table either. All least not that I have noticed 🙂
----------------------------------------------------
December 17, 2015 at 1:14 pm
MMartin1 (12/17/2015)
Does the SELECT 1 provide a big performance gain or just prevents a call to the disk that may get delayed?
Neither. It's there to show readers that EXISTS doesn't care about column names. You could do EXISTS (SELECT 1/0 ...) if you wanted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2015 at 1:38 pm
GilaMonster (12/17/2015)
MMartin1 (12/17/2015)
Does the SELECT 1 provide a big performance gain or just prevents a call to the disk that may get delayed?Neither. It's there to show readers that EXISTS doesn't care about column names. You could do EXISTS (SELECT 1/0 ...) if you wanted.
Someone who shall remain nameless had a good blog post on this subject!
http://sqlinthewild.co.za/index.php/2011/04/05/to-top-or-not-to-top-an-exists/#more-988
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 17, 2015 at 4:51 pm
Michael L John (12/17/2015)
GilaMonster (12/17/2015)
MMartin1 (12/17/2015)
Does the SELECT 1 provide a big performance gain or just prevents a call to the disk that may get delayed?Neither. It's there to show readers that EXISTS doesn't care about column names. You could do EXISTS (SELECT 1/0 ...) if you wanted.
Someone who shall remain nameless had a good blog post on this subject!
http://sqlinthewild.co.za/index.php/2011/04/05/to-top-or-not-to-top-an-exists/#more-988
Great article, and informative. Thank you both for that.
----------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply