Insert ROWS if not exists SQL-CodePudding

I want to insert rows that not exist in my table

INSERT INTO [Cop].[fact_capacidadOperativa] ([id_empleado],[id_proyecto],[id_rol],[id_categoria],[id_subCategoria],[id_portfolio]
,[id_programa],[horas],[horasPlan],[id_semanaAño],[id_torre])
/*SELECT * FROM [Cop].[timeSheet]*/
SELECT id_empleado,id_proyecto,id_rol,id_categoria,id_subCategoria,
id_portfolio,id_programa,[Cop].[timeSheet].horas,[Cop].[ListaEmpleados].horaPlan,
[Cop].[timeSheet].nroSemana [Cop].[timeSheet].año AS id_semanaAño,id_torre FROM [Cop].[timeSheet]
JOIN [Cop].[ListaEmpleados] 
ON [Cop].[timeSheet].nombre = [Cop].[ListaEmpleados].recurso
LEFT JOIN [Cop].[ListaProyectos]
ON [Cop].[timeSheet].[proyecto] = [Cop].[ListaProyectos].proyecto
JOIN [Cop].[dim_empleados]
ON [Cop].[timeSheet].nombre = [Cop].[dim_empleados].empleado
LEFT JOIN [Cop].[dim_proyectos]
ON [Cop].[timeSheet].proyecto = [Cop].[dim_proyectos].proyecto
JOIN [Cop].[dim_roles]
ON [Cop].[ListaEmpleados].rol = [Cop].[dim_roles].rol
LEFT JOIN [Cop].[dim_categorias]
ON [Cop].[ListaProyectos].categoria = [Cop].[dim_categorias].categoria
LEFT JOIN [Cop].[dim_subCategorias]
ON [Cop].[ListaProyectos].subcategoria = [Cop].[dim_subCategorias].subCategoria
left JOIN [Cop].[dim_portfolios]
ON [Cop].[ListaProyectos].[portfolio] = [Cop].[dim_portfolios].portfolio
LEFT JOIN [Cop].[dim_programas]
ON [Cop].[ListaProyectos].program = [Cop].[dim_programas].programa
JOIN [Cop].[dim_torres]
ON [Cop].[timeSheet].torre = [Cop].[dim_torres].torre

imagen

Insert the values that not exist in [Cop].[fact_capacidadOperativa], i don t know if i need to use the where. this insert into is from one stored procedure that i m making

I think something like this

INSERT INTO [Cop].[fact_capacidadOperativa] ([id_empleado],[id_proyecto],[id_rol],[id_categoria],[id_subCategoria],[id_portfolio]
,[id_programa],[horas],[horasPlan],[id_semanaAño],[id_torre])
/*SELECT * FROM [Cop].[timeSheet]*/
select * from (SELECT id_empleado,id_proyecto,id_rol,id_categoria,id_subCategoria,
id_portfolio,id_programa,[Cop].[timeSheet].horas,[Cop].[ListaEmpleados].horaPlan,
[Cop].[timeSheet].nroSemana [Cop].[timeSheet].año AS id_semanaAño,id_torre FROM [Cop].[timeSheet]
JOIN [Cop].[ListaEmpleados] 
ON [Cop].[timeSheet].nombre = [Cop].[ListaEmpleados].recurso
JOIN [Cop].[ListaProyectos]
ON [Cop].[timeSheet].[proyecto] = [Cop].[ListaProyectos].proyecto
JOIN [Cop].[dim_empleados]
ON [Cop].[timeSheet].nombre = [Cop].[dim_empleados].empleado
JOIN [Cop].[dim_proyectos]
ON [Cop].[timeSheet].proyecto = [Cop].[dim_proyectos].proyecto
JOIN [Cop].[dim_roles]
ON [Cop].[ListaEmpleados].rol = [Cop].[dim_roles].rol
JOIN [Cop].[dim_categorias]
ON [Cop].[ListaProyectos].categoria = [Cop].[dim_categorias].categoria
JOIN [Cop].[dim_subCategorias]
ON [Cop].[ListaProyectos].subcategoria = [Cop].[dim_subCategorias].subCategoria
JOIN [Cop].[dim_portfolios]
ON [Cop].[ListaProyectos].[portfolio] = [Cop].[dim_portfolios].portfolio
JOIN [Cop].[dim_programas]
ON [Cop].[ListaProyectos].program = [Cop].[dim_programas].programa
JOIN [Cop].[dim_torres]
ON [Cop].[timeSheet].torre = [Cop].[dim_torres].torre) a
where not exists (select 1 from [Cop].[fact_capacidadOperativa] b where a.id_empleado = b.id_empleado 
                    and a.id_proyecto = b.id_proyecto  and a.id_rol = b.id_rol and a.id_categoria = b.id_categoria
                    and a.id_subCategoria = b.id_subCategoria and a.id_portfolio = b.id_portfolio and a.id_programa = b.id_programa and
                    a.horas = b.horas and a.horaPlan = b.horasPlan and a.id_torre = b.id_torre and a.id_semanaAño = b.id_semanaAño);

But i don t know it s the best way

CodePudding user response：

As mentioned in the comment, we can create a UNIQUE constraint on all affected columns.

Then we use INSERT IGNORE to only insert valid rows in our target table from the other table(s).

Assume we have two tables, both with column1, column2 and column3 as integers. Now we want to insert all data from one of these tables to the other, but apply a unique constraint on column1 and column2 of the target table.

This will create the unique constraint:

ALTER TABLE yourtable 
ADD UNIQUE (column1, column2);

Then this insert commands will only insert valid data in the table which does not violate the unqiue constraint:

INSERT IGNORE INTO yourtable 
  (SELECT column1, column2, column3 FROM anothertable);

I created a sample fiddle which shows the complete behaviour with and without this constraint and with or without the usage of INSERT IGNORE.

So replicate this idea here: db<>fiddle

Here is the documentation of INSERT IGNORE: documentation

All you need to do is to create the correct unique constraint for your current situation and to make sure your insert command is basically correct. Then only valid rows will be inserted, as shown in the fiddle.