April 13, 2016 at 4:16 pm
I want to insert only unique tracerNo field if it doesn't exist. Here is my code. This is not working for some reason. When I run this nothing get inserted.
insert into Tracer
(QuoteNo, VendID, VendOrdNo, Carrier, TracerNo)
SELECT 22943,119,'22943-JDE','UPS','1Z9306X40344831'
WHERE NOT EXISTS
(Select * from Tracer Where TracerNo='1Z9306X40344831')
PLease advise
April 13, 2016 at 4:45 pm
shezi (4/13/2016)
I want to insert only unique tracerNo field if it doesn't exist. Here is my code. This is not working for some reason. When I run this nothing get inserted.insert into Tracer
(QuoteNo, VendID, VendOrdNo, Carrier, TracerNo)
SELECT 22943,119,'22943-JDE','UPS','1Z9306X40344831'
WHERE NOT EXISTS
(Select * from Tracer Where TracerNo='1Z9306X40344831')
PLease advise
Try this:
with basedata as (
SELECT *
from (values (22943,119,'22943-JDE','UPS','1Z9306X40344831'))dt(QuoteNo, VendID, VendOrdNo, Carrier, TracerNo)
)
insert into Tracer (QuoteNo, VendID, VendOrdNo, Carrier, TracerNo)
select QuoteNo, VendID, VendOrdNo, Carrier, TracerNo
from basedata bd
WHERE NOT EXISTS(Select * from Tracer t Where t.TracerNo = bd.TracerNo);
April 13, 2016 at 5:32 pm
Did you try to run the EXISTS part separately?
What does it return?
Select * from Tracer Where TracerNo='1Z9306X40344831'
_____________
Code for TallyGenerator
April 14, 2016 at 10:06 am
shezi (4/13/2016)
I want to insert only unique tracerNo field if it doesn't exist. Here is my code. This is not working for some reason. When I run this nothing get inserted.insert into Tracer
(QuoteNo, VendID, VendOrdNo, Carrier, TracerNo)
SELECT 22943,119,'22943-JDE','UPS','1Z9306X40344831'
WHERE NOT EXISTS
(Select * from Tracer Where TracerNo='1Z9306X40344831')
PLease advise
Is there already a row with the mentioned TracerNo in the Tracer table? If so, then the code is working as programmed, because you tell it to only insert the row if it does not exist yet.
If there is no row with that TracerNo in the Tracer table yet, then something weird is happening because the code looks okay to me. Did you get error messages? Have you checked whether there are any triggers on the Tracer table? Have you checked all data types (and lengths of char/varchar columns)?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply