/****** Script do comando SelectTopNRows de SSMS  ******/
SELECT 

	
	concat('insert into TBMODELO_CONSUMO_COR (IMP, MODELO_ID, COR_ID, PRODUTO_ID, TAM, SETOR_ID, MODELO_ORIGEM, PRODUTO_ORIGEM,',
            'TAM_ORIGEM, MODELO_DESTINO, PRODUTO_DESTINO, TAM_DESTINO, MATRIZ_ID,',
            'VALOR_BASE, NIVEL, FLAG, T01, T02, T03, T04, T05, T06, T07, T08, T09, T10,',
            'T11, T12, T13, T14, T15, T16, T17, T18, T19, T20, QUANTIDADE, OBSERVACAO,',
            'FATOR_CONVERSAO, PERCENTUAL_CONSUMO, ALOCACAO, VB01, VB02, VB03, VB04, VB05,',
            'VB06, VB07, VB08, VB09, VB10, VB11, VB12, VB13, VB14, VB15, VB16, VB17,',
            'VB18, VB19, VB20, FATOR_MATRIZ, HABILITA_GERAR_CONSUMO)',

			' values (1, coalesce((select first 1 m.codigo from tbmodelo m where m.old_codigo = ''',a.[Referencia],''' and imp = 1),0), ',b.Cor1,', (select first 1 p.codigo from tbproduto p where p.old_modelo_id = ',[CodigoAlmox],' and p.cor_codigo = ',[CorAlmox],'),',
			'0, ',
			
			IIF([Processo] = 1 , 16, 
			IIF([Processo] = 2 , 12, 
			IIF([Processo] = 3 , 13, 
			IIF([Processo] = 23, 14, 
			IIF([Processo] = 9 , 15, 
			IIF([Processo] = 12, 17, 0))))))
			
			,', 0, 0, 0,0, 0, 0, 0, 0, 0, 0, ',
			'',IIF(SUM([Qtde])> 0, SUM([Qtde]), SUM([Tam01])) ,', ',IIF(SUM([Qtde])> 0, SUM([Qtde]), SUM([Tam02])) ,', ',IIF(SUM([Qtde])> 0, SUM([Qtde]), SUM([Tam03])) ,', ',IIF(SUM([Qtde])> 0, SUM([Qtde]), SUM([Tam04])) ,', ',IIF(SUM([Qtde])> 0, SUM([Qtde]), SUM([Tam05])) ,',',
			'',IIF(SUM([Qtde])> 0, SUM([Qtde]), SUM([Tam06])) ,', ',IIF(SUM([Qtde])> 0, SUM([Qtde]), SUM([Tam07])) ,', ',IIF(SUM([Qtde])> 0, SUM([Qtde]), SUM([Tam08])) ,', ',IIF(SUM([Qtde])> 0, SUM([Qtde]), SUM([Tam09])) ,', ',IIF(SUM([Qtde])> 0, SUM([Qtde]), SUM([Tam10])) ,',',
			'0,0,0,0,0,0,0,0,0,0,',SUM([Qtde]),',',
			''''', 1, 100, 1, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, 1,1)',
			';') as SQL

  FROM [Sitex].[dbo].[FichaTecnicaItem] a, [Sitex].[dbo].[FichaTecnicaCombinacao] b
  where [CorDPA] > 0
  and b.Referencia = a.Referencia
  and b.Cor = a.CorDPA

  GROUP BY [Processo], [CodigoAlmox], [CorAlmox], a.[Referencia], [Cor1]