Ernesto
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,desc ripcion,pr oveedor,ti pmater)
values ( 'HV9900', 'PZA', 'VALVULAS DE CONTROL ON-OFF', 'ADOBE SYSTEMS INCORPORATED', 'INSTRUMENTACION')
where materiales.tag <>'HV9900')
tsm
how can i achive this query
i mean, insert the element when is not already in
insert into materiales(tag,unidad,desc
values ( 'HV9900', 'PZA', 'VALVULAS DE CONTROL ON-OFF', 'ADOBE SYSTEMS INCORPORATED', 'INSTRUMENTACION')
where materiales.tag <>'HV9900')
tsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can also use MERGE. This makes code easy to understand
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,desc
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,desc
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,desc
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 */