Link to home
Start Free TrialLog in
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
ASKER CERTIFIED SOLUTION
Avatar of dsacker
dsacker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of winheim
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 */
You can also use MERGE. This makes code easy to understand