if does not exist then insert row

Question

Post reply

if does not exist then insert row

Oracle765

SSCrazy

Points: 2862
More actions
May 7, 2013 at 4:49 pm

#293610

Hi Professionals.
I have two table which are identical apart from the data in them
lookuptable with columns
raw_sw_manufacturer, amended_sw_manufacturer,
raw_product_name, amended_product_name,
raw_product_version, amended_product_version
lookuptable1
raw_sw_manufacturer, amended_sw_manufacturer,
raw_product_name, amended_product_name,
raw_product_version, amended_product_version
I am looking to insert rows into lookuptable based on
if
lookuptable1's
raw_sw_manufacturer,raw_product_name, raw_product_version exist in the lookuptable then
do nothing
else
insert the row
end if
is this possible
thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

suneet.mlvy SSC Eights! Points: 964 More actions · Answer 1

Hi Alan,

insert into lookuptable

select * from lookuptable1

where raw_sw_manufacturer not in (select raw_sw_manufacturer from lookuptable ) and

raw_product_name not in (select raw_product_name from lookuptable ) and

raw_product_version not in (select raw_product_version from lookuptable )

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

INSERT INTO lookuptable (

raw_sw_manufacturer, amended_sw_manufacturer,

raw_product_name, amended_product_name,

raw_product_version, amended_product_version)

SELECT

raw_sw_manufacturer, amended_sw_manufacturer,

raw_product_name, amended_product_name,

raw_product_version, amended_product_version

FROM lookuptable1 l1

WHERE NOT EXISTS (

SELECT 1

FROM lookuptable l

WHERE l.raw_sw_manufacturer = l1.raw_sw_manufacturer

AND l.raw_product_name = l1.raw_product_name

AND l.raw_product_version = l1.raw_product_version)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Also, you could use MERGE, which can be extended to update non-matched records or/and delete some:

MERGE lookuptable AS dest

FROM lookuptable1 AS src

ON ( dest.raw_sw_manufacturer = src.raw_sw_manufacturer

AND dest.raw_product_name = src.raw_product_name

AND dest.raw_product_version = src.raw_product_version )

WHEN NOT MATCHED THEN

INSERT (raw_sw_manufacturer, amended_sw_manufacturer,

raw_product_name, amended_product_name,

raw_product_version, amended_product_version)

VALUES (src.raw_sw_manufacturer, src.amended_sw_manufacturer,

src.raw_product_name, src.amended_product_name,

src.raw_product_version, src.amended_product_version)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]