
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_md_types_from_act_types]

AS

DECLARE @ais_actor_prefix varchar(20)
SET @ais_actor_prefix = 'ais_actor_'

INSERT INTO md_types	(	typ_type_name,									typ_l1, typ_l2, typ_l3,			typ_is_string,	typ_is_dyn, typ_len,	typ_authorised, typ_c,	typ_sys,	typ_sql )
select						@ais_actor_prefix + lower(at.at_type_code),		at.at_l1, at.at_l2, at.at_l3,	0,				1,			4,			1,				'int',	0,			'int'
from act_types at
WHERE NOT EXISTS(SELECT 1 FROM md_types WHERE typ_type_name = @ais_actor_prefix + at.at_type_code)

DELETE mt
FROM md_types mt
WHERE mt.typ_type_name LIKE @ais_actor_prefix + '%' AND NOT EXISTS(SELECT 1 FROM act_types WHERE at_type_code = REPLACE(mt.typ_type_name, @ais_actor_prefix, ''))


GO
