
DECLARE @recId int, @productItemId int, @unitEAN nvarchar(max), @itemId int,
@uniqueName nvarchar(max), @surfaceId int, @uniqueNameSurfaceFieldId int,
@EANSurfaceFieldId int, @parentSurfaceFieldId int; 
select @surfaceId = recId from pal_Surface where name = 'ProductAlternateEAN';
select @uniqueNameSurfaceFieldId = recId from pal_SurfaceField
where surfaceFieldName = 'ProductAlternateEAN_AlternateEANDetails_UniqueName'
and surfaceId = @surfaceId;
select @EANSurfaceFieldId = recId from pal_SurfaceField
where surfaceFieldName = 'ProductAlternateEAN_AlternateEANDetails_AlternateEAN'
and surfaceId = @surfaceId;
select @parentSurfaceFieldId = recId from pal_SurfaceField
where surfaceFieldName = 'ParentSurfaceItemId'
and surfaceId = @surfaceId;
SET rowcount 0

select Products_ProductDetails_UniqueName, Products_ProductDetails_UnitEAN, 
itemID, recId 
into #product
from publ_Products
where isActive = 1;

SET rowcount 1
select @recId = recId,
@uniqueName = Products_ProductDetails_UniqueName,
@unitEAN = Products_ProductDetails_UnitEAN,
@productItemId = itemID
from #product;

WHILE @@rowcount <> 0
BEGIN
    SET rowcount 0
	delete #product where recId = @recId

	IF NOT EXISTS (select recId from pal_SurfaceFieldData
	where surfaceFieldID = @EANSurfaceFieldId
	and surfaceId = @surfaceId
	and surfaceFieldValueChar = @unitEAN)
	BEGIN
		--create item
		INSERT INTO [dbo].[pal_SurfaceItem]
			   ([surfaceId]
			   ,[dateCreated]
			   ,[isActive]
			   ,[createdBy]
			   ,[updatedBy]
			   ,[dateUpdated]
			   ,[isDeleted]
			   ,[isWizardCompleted]
			   ,[lastTabCompleted]
			   ,[userLink]
			   ,[surfaceLinkItems])
		 VALUES
			   (@surfaceId
			   ,GETDATE()
			   ,1
			   ,217
			   ,0
			   ,CAST('1900-01-01 00:00:00' AS DATETIME)
			   ,0
			   ,0
			   ,0
			   ,0
			   ,'');
		SET @itemID = SCOPE_IDENTITY();

		--create ProductAlternateEAN_AlternateEANDetails_UniqueName data
		INSERT INTO [dbo].[pal_SurfaceFieldData]
			   ([surfaceId]
			   ,[surfaceItemId]
			   ,[surfaceFieldID]
			   ,[surfaceFieldValueDate]
			   ,[surfaceFieldValueDecimal]
			   ,[surfaceFieldValueChar]
			   ,[surfaceFieldValueNum]
			   ,[surfaceFieldValueBool]
			   ,[surfaceFieldValueTime]
			   ,[surfaceFieldLookupID])
		VALUES  (@surfaceId
				,@itemId
				,@uniqueNameSurfaceFieldId
				,'1900-01-01 00:00:00.000'
				,0.00
				,@uniqueName
				,0
				,0
				,'1900-01-01 00:00:00.000'
				,0);
	
		--create ProductAlternateEAN_AlternateEANDetails_AlternateEAN data
		INSERT INTO [dbo].[pal_SurfaceFieldData]
			   ([surfaceId]
			   ,[surfaceItemId]
			   ,[surfaceFieldID]
			   ,[surfaceFieldValueDate]
			   ,[surfaceFieldValueDecimal]
			   ,[surfaceFieldValueChar]
			   ,[surfaceFieldValueNum]
			   ,[surfaceFieldValueBool]
			   ,[surfaceFieldValueTime]
			   ,[surfaceFieldLookupID])
		VALUES  (@surfaceId
				,@itemId
				,@EANSurfaceFieldId
				,'1900-01-01 00:00:00.000'
				,0.00
				,@unitEAN
				,0
				,0
				,'1900-01-01 00:00:00.000'
				,0);

		--create ParentSurfaceItemId data
		INSERT INTO [dbo].[pal_SurfaceFieldData]
           ([surfaceId]
           ,[surfaceItemId]
           ,[surfaceFieldID]
           ,[surfaceFieldValueDate]
           ,[surfaceFieldValueDecimal]
           ,[surfaceFieldValueChar]
           ,[surfaceFieldValueNum]
           ,[surfaceFieldValueBool]
           ,[surfaceFieldValueTime]
           ,[surfaceFieldLookupID])
	VALUES  (@surfaceId
			,@itemId
			,@parentSurfaceFieldId
			,'1900-01-01 00:00:00.000'
			,0.00
			,''
			,@productItemId
			,0
			,'1900-01-01 00:00:00.000'
			,0);
	
		--update qt table
		EXEC sp_SaveSurfaceItemQueryTable @SurfaceID=@surfaceId,@UserLink=0,@UserId=219,@ItemID=@itemId;
	END;
	SET rowcount 1
	select @recId = recId,
	@uniqueName = Products_ProductDetails_UniqueName,
	@unitEAN = Products_ProductDetails_UnitEAN,
	@productItemId = itemID
	from #product;

END
set rowcount 0;
DROP TABLE #product;
