SQL Procedure für Anpassung der alte/neue Artikelnummer

USE [skatech]
GO
/****** Object:  StoredProcedure [dbo].[skatechSemikronMatch]    Script Date: 29.04.2026 21:33:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[skatechSemikronMatch] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * 
	from (select * from itemimport where catalogid in (select catalogid from catalogs where supplierid=64)) as sneu
	inner join 
	(select * from itemimport where catalogid in (select catalogid from catalogs where supplierid<>64)) as salt
	inner join 
	skatechSemikron 
	on salt.externalid=skatechSemikron.oldexternalId
	on sneu.externalid=skatechSemikron.newexternalid
	inner join items on salt.itemnr=items.itemnr
	where sneu.itemnr is null


	update items set matchcode=sneu.externalID 
	from (select * from itemimport where catalogid in (select catalogid from catalogs where supplierid=64)) as sneu
	inner join 
	(select * from itemimport where catalogid in (select catalogid from catalogs where supplierid<>64)) as salt
	inner join 
	skatechSemikron 
	on salt.externalid=skatechSemikron.oldexternalId
	on sneu.externalid=skatechSemikron.newexternalid
	inner join items on salt.itemnr=items.itemnr
	where sneu.itemnr is null

	update sneu set itemnr=items.itemnr 
	from (select * from itemimport where catalogid in (select catalogid from catalogs where supplierid=64)) as sneu
	inner join 
	(select * from itemimport where catalogid in (select catalogid from catalogs where supplierid<>64)) as salt
	inner join 
	skatechSemikron 
	on salt.externalid=skatechSemikron.oldexternalId
	on sneu.externalid=skatechSemikron.newexternalid
	inner join items on salt.itemnr=items.itemnr
	where sneu.itemnr is null
	
	-- Kategorien richtigstellen

	update itemimport set category=371
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	where catalogid in (select catalogid from catalogs where supplierid=64)


	update itemimport set category=298
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Product line'
	and itemimportproperties.svalue = 'SEMIPACK'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=202
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Product line'
	and itemimportproperties.svalue = 'SEMIPONT'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=649
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Product line'
	and itemimportproperties.svalue = 'Driver Core'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=649
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Product line'
	and itemimportproperties.svalue = 'Driver'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=649
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Product line'
	and itemimportproperties.svalue = 'Adapter Board'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=241
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Product line'
	and itemimportproperties.svalue = 'SKiiP 3'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=241
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Product line'
	and itemimportproperties.svalue = 'SKiiP 4'
	where catalogid in (select catalogid from catalogs where supplierid=64)
	

	update itemimport set category=241
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Technology'
	and itemimportproperties.svalue = 'IGBT'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=266
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Technology'
	and itemimportproperties.svalue = 'MOSFET'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=266
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Technology'
	and itemimportproperties.svalue = 'Hybrid SiC'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=266
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Technology'
	and itemimportproperties.svalue = 'SiC MOSFET'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=202
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Technology'
	and itemimportproperties.svalue = 'Miniature Bridge'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=202
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Technology'
	and itemimportproperties.svalue = 'Bridge'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=298
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Technology'
	and itemimportproperties.svalue = 'Thyristor / Diode'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=210
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Technology'
	and itemimportproperties.svalue = 'Diodes'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=210
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Technology'
	and itemimportproperties.svalue = 'Thyristor'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=241
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Technology'
	and itemimportproperties.svalue = 'SKiiP Utilities'
	where catalogid in (select catalogid from catalogs where supplierid=64)

	update itemimport set category=241
	from itemimport inner join itemimportproperties 
	on itemimport.importID=itemimportproperties.importID
	and itemimportproperties.propertyname='Technology'
	and itemimportproperties.svalue = 'Shunt'
	where catalogid in (select catalogid from catalogs where supplierid=64)

END