
CREATE TABLE #productRawData (
	[ProductRawData_ProductRawData_Brand] [nvarchar](max) NULL,
	[ProductRawData_ProductRawData_Case] [nvarchar](max) NULL,
	[ProductRawData_ProductRawData_Category] [nvarchar](max) NULL,
	[ProductRawData_ProductRawData_ProductDescription] [nvarchar](max) NULL,
	[ProductRawData_ProductRawData_Shrink] [nvarchar](max) NULL,
	[ProductRawData_ProductRawData_SupplierName] [nvarchar](max) NULL,
	[ProductRawData_ProductRawData_UnitEAN] [nvarchar](max) NULL,
	[ProductRawData_ProductRawData_UnitSize] [nvarchar](max) NULL,
	[ProductRawData_ProductRawData_Variant] [nvarchar](max) NULL,
	[recId] [int] NOT NULL,
)

DECLARE @recId int, @itemID int, @brand nvarchar(max), @case int
, @category nvarchar(max),@desc nvarchar(max), @surfaceId int, @shrink nvarchar(max)
, @supplier nvarchar(max), @unitEAN nvarchar(max), @unitSize nvarchar(max), @variant nvarchar(max);
SET @surfaceId = 3103;
SET rowcount 0

BULK INSERT #productRawData
FROM 'C:\REAPtech - Data Master (2019.11.29) v2.csv'
WITH
(
  FIRSTROW = 2,
  DATAFILETYPE='widechar', -- UTF-16
  FIELDTERMINATOR = ';',
  ROWTERMINATOR = '\n',
  TABLOCK,
  KEEPNULLS -- Treat empty fields as NULLs.
)

SET rowcount 1
select @recId = recId,
@brand = RTRIM(ProductRawData_ProductRawData_Brand),
@category = RTRIM(ProductRawData_ProductRawData_Category),
@desc = RTRIM(ProductRawData_ProductRawData_ProductDescription),
@variant = RTRIM(ProductRawData_ProductRawData_Variant),
@supplier = RTRIM(ProductRawData_ProductRawData_SupplierName),
@unitEAN = RTRIM(ProductRawData_ProductRawData_UnitEAN),
@unitSize = RTRIM(ProductRawData_ProductRawData_UnitSize),
@case = CAST(RTRIM(ProductRawData_ProductRawData_Case) AS INT),
@shrink = CAST(RTRIM(ProductRawData_ProductRawData_Shrink) AS INT)
from #productRawData;

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

	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();

	INSERT INTO [dbo].[publ_ProductRawData]
		([itemID]
        ,[isActive]
        ,[surfaceId]
        ,[ProductRawData_ProductRawData_ProductDescription]
        ,[ProductRawData_ProductRawData_Variant]
        ,[ProductRawData_ProductRawData_UnitEAN]
        ,[ProductRawData_ProductRawData_UnitSize]
        ,[ProductRawData_ProductRawData_Shrink]
        ,[ProductRawData_ProductRawData_Case]
        ,[ProductRawData_ProductRawData_SupplierName]
        ,[ProductRawData_ProductRawData_Brand]
        ,[ProductRawData_ProductRawData_Category])
    VALUES
        (@itemID
        ,1
        ,@surfaceId
        ,@desc
        ,@variant
        ,@unitEAN
        ,@unitSize
        ,@shrink
        ,@case
        ,@supplier
        ,@brand
        ,@category);
	SET rowcount 1
	select @recId = recId,
	@brand = RTRIM(ProductRawData_ProductRawData_Brand),
	@category = RTRIM(ProductRawData_ProductRawData_Category),
	@desc = RTRIM(ProductRawData_ProductRawData_ProductDescription),
	@variant = RTRIM(ProductRawData_ProductRawData_Variant),
	@supplier = RTRIM(ProductRawData_ProductRawData_SupplierName),
	@unitEAN = RTRIM(ProductRawData_ProductRawData_UnitEAN),
	@unitSize = RTRIM(ProductRawData_ProductRawData_UnitSize),
	@case = CAST(RTRIM(ProductRawData_ProductRawData_Case) AS INT),
	@shrink = CAST(RTRIM(ProductRawData_ProductRawData_Shrink) AS INT)
	from #productRawData;

END
set rowcount 0;
DROP TABLE #productRawData;
