create  function tempFunc_GetColunasTabela(@TableName varchar(50), @ignoredColumn varchar(50) = '', @ignoredColumn2 varchar(50) = '') returns varchar(max)
as
begin
	declare @Colunas varchar(max)
	set @Colunas = (SELECT CONCAT(COLUMN_NAME, ',')
              from INFORMATION_SCHEMA.COLUMNS
             where Upper(TABLE_NAME) = Upper(@TableName)
               and COLUMN_NAME not IN('ID_EMP',
                                      concat('ID', substring(@TableName, CHARINDEX('_', @TableName), LEN(@TableName))),
									  @ignoredColumn2
                                     )
			   and COLUMN_NAME not like @ignoredColumn
               for xml path(''));

	return SUBSTRING(@Colunas, 1, LEN(@Colunas) - 1);
end
go

Declare @EmpresaBase Int = 3 /*Empresa que será usada como base para os registros ŕ serem inseridos*/
declare @EmpresaNova Int = 4 /*Empresa nova que será inserida e referenciada nos novos registros*/

declare @cmd nvarchar(max)

/*Clonar Empresa (Nova ID_EMP = @EmpresaNova) (Base ID_EMP = @EmpresaBase)*/
set @cmd =
   ' insert into CG_EMP (ID_EMP, ' + dbo.tempFunc_GetColunasTabela('CG_EMP', '', '') + ')' +
   ' select                   '+cast(@EmpresaNova as varchar(10))+', ' + dbo.tempFunc_GetColunasTabela('CG_EMP', '', '') + ' from CG_EMP E WHERE E.ID_EMP = '+cast(@EmpresaBase as varchar(10))+' and not exists (select 1 from CG_EMP e2 where e2.ID_EMP = '+cast(@EmpresaNova as varchar(10))+')'
exec SP_EXECUTESQL @cmd;

set @cmd =
   ' insert into CG_EMP_PAR (ID_EMP_PAR, ID_EMP, ' + dbo.tempFunc_GetColunasTabela('CG_EMP_PAR', '', '') + ')' +
   ' select next value for G_ID_EMP_PAR,      '+cast(@EmpresaNova as varchar(10))+', ' + dbo.tempFunc_GetColunasTabela('CG_EMP_PAR', '', '') + ' from CG_EMP_PAR E WHERE E.ID_EMP = '+cast(@EmpresaBase as varchar(10))+' and not exists (select 1 from CG_EMP_PAR e2 where e2.ID_EMP = '+cast(@EmpresaNova as varchar(10))+')'
exec SP_EXECUTESQL @cmd;
------------------------------------------------------

/*Clonar Comercial do Produto*/
set @cmd =
   ' insert into EQ_PROD_COM (ID_PROD_COM, ID_EMP, ' + dbo.tempFunc_GetColunasTabela('EQ_PROD_COM', '', '') + ')' +
   ' select next value for  G_ID_PROD_COM,      '+cast(@EmpresaNova as varchar(10))+', ' + dbo.tempFunc_GetColunasTabela('EQ_PROD_COM', '', '') + ' from EQ_PROD_COM PC WHERE PC.ID_EMP = '+cast(@EmpresaBase as varchar(10))+' and not exists (select 1 from EQ_PROD_COM pc2 where pc2.ID_PROD = PC.ID_PROD and pc2.ID_EMP = '+cast(@EmpresaNova as varchar(10))+')'
exec SP_EXECUTESQL @cmd;

/*Clonando chaves base e associando com chave nova referente a id de quantidade*/
if not exists (select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'tempEQ_PROD_QTDE')
  create table tempEQ_PROD_QTDE(ID_PROD_QTDE__BASE Int, ID_PROD_QTDE__NOVO Int)

    insert into tempEQ_PROD_QTDE (ID_PROD_QTDE__BASE, ID_PROD_QTDE__NOVO)
    select ID_PROD_QTDE, next value for G_ID_PROD_QTDE from EQ_PROD_QTDE PQ WHERE PQ.ID_EMP = @EmpresaBase and not exists (select 1 from EQ_PROD_QTDE pq2 where pq2.ID_PROD = PQ.ID_PROD and pq2.ID_EMP = @EmpresaNova)


/*Clonar Quantidade do Produto*/
set @cmd =
   ' insert into EQ_PROD_QTDE (ID_PROD_QTDE, ID_EMP, QTDE, ' + dbo.tempFunc_GetColunasTabela('EQ_PROD_QTDE', 'QTDE', '') + ')' +
   ' select       tempPQ.ID_PROD_QTDE__NOVO,      '+cast(@EmpresaNova as varchar(10))+',    0, ' + dbo.tempFunc_GetColunasTabela('EQ_PROD_QTDE', 'QTDE', '') + ' from EQ_PROD_QTDE PQ inner join tempEQ_PROD_QTDE tempPQ ON tempPQ.ID_PROD_QTDE__BASE = PQ.ID_PROD_QTDE WHERE PQ.ID_EMP = '+cast(@EmpresaBase as varchar(10))+' and not exists (select 1 from EQ_PROD_QTDE pq2 where pq2.ID_PROD = PQ.ID_PROD and pq2.ID_EMP = '+cast(@EmpresaNova as varchar(10))+')'
exec SP_EXECUTESQL @cmd;


/*Guardando lotes inseridos*/
if not exists (select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'tempEQ_PROD_QTDE_LOTE')
  create table tempEQ_PROD_QTDE_LOTE(ID_PROD_QTDE_LOTE__INSERIDO Int)

/*Clonar Lote do Produto*/
set @cmd =
   ' insert into EQ_PROD_QTDE_LOTE (ID_PROD_QTDE_LOTE,              ID_PROD_QTDE, QTDE, QTDE_E, QTDE_S, ' + dbo.tempFunc_GetColunasTabela('EQ_PROD_QTDE_LOTE', 'QTDE%', 'ID_PROD_QTDE') + ')' +
   ' output inserted.ID_PROD_QTDE_LOTE into tempEQ_PROD_QTDE_LOTE ' +
   ' select next value for        G_ID_PROD_QTDE_LOTE, tempPQ.ID_PROD_QTDE__NOVO,    0,      0,      0, ' + dbo.tempFunc_GetColunasTabela('EQ_PROD_QTDE_LOTE', 'QTDE%', 'ID_PROD_QTDE') +
   '   from EQ_PROD_QTDE_LOTE PQL
      inner join tempEQ_PROD_QTDE tempPQ ON tempPQ.ID_PROD_QTDE__BASE = PQL.ID_PROD_QTDE
      WHERE EXISTS (select 1
                      from EQ_PROD_QTDE PQ
                     where PQ.ID_PROD_QTDE = PQL.ID_PROD_QTDE
                       and PQ.ID_EMP = '+cast(@EmpresaBase as varchar(10))+') '
exec SP_EXECUTESQL @cmd;