Avatar of Ernesto
ErnestoFlag for Mexico asked on

insert where is not in

hi you experts
how can i achive this query

i mean, insert the element when is not already in


insert into materiales(tag,unidad,descripcion,proveedor,tipmater)
values ( 'HV9900', 'PZA', 'VALVULAS DE CONTROL ON-OFF', 'ADOBE SYSTEMS INCORPORATED', 'INSTRUMENTACION')
where materiales.tag <>'HV9900')

tsm
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
sameer2010

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
dsacker

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
winheim

One way to do this would be this:

CREATE Table materiales
(
 tag nvarchar(100) , /* unique?*/
 unidad nvarchar(100),
 descripcion nvarchar(255),
 proveedor nvarchar(255),
 tipmater nvarchar(255),
)

/*
* Add anything
*/
INSERT INTO materiales(tag,unidad,descripcion,proveedor,tipmater)
values ( 'HV9900', 'PZA', 'VALVULAS DE CONTROL ON-OFF', 'ADOBE SYSTEMS INCORPORATED', 'INSTRUMENTACION')


/*
* You can use Left Join and a sub query
*/

-- THIS query does not add a new row
insert into materiales(tag,unidad,descripcion,proveedor,tipmater)

SELECT * FROM
(
      SELECT 'HV9900' AS tag, 'PZA' AS unidad, 'VALVULAS DE CONTROL ON-OFF' AS descripcion, 'ADOBE SYSTEMS INCORPORATED' AS proveedor, 'INSTRUMENTACION' AS tipmater      
) to_insert
LEFT JOIN materiales m
ON to_insert.tag = m.tag
WHERE m.tag IS NULL /* if returns NULL then you know that the line you'd like to add is not already present in the table materiales */



-- THIS query adds a new row
insert into materiales(tag,unidad,descripcion,proveedor,tipmater)

SELECT * FROM
(
      SELECT 'HV9901' AS tag, 'PZA' AS unidad, 'VALVULAS DE CONTROL ON-OFF' AS descripcion, 'ADOBE SYSTEMS INCORPORATED' AS proveedor, 'INSTRUMENTACION' AS tipmater      
) to_insert
LEFT JOIN materiales m
ON to_insert.tag = m.tag
WHERE m.tag IS NULL /* if returns NULL then you know that the line you'd like to add is not already present in the table materiales */
sameer2010

You can also use MERGE. This makes code easy to understand
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy