
CREATE TABLE #masters (
	[MasterName] [nvarchar](max) NULL,
	[Brand] [nvarchar](max) NULL,
	[Department] [nvarchar](max) NULL,
	[Subdepartment] [nvarchar](max) NULL,
	[Description] [nvarchar](max) NULL,
	[UnitSize] [nvarchar](max) NULL,
	[ShrinkQuantity] [nvarchar](max) NULL,
	[CaseQuantity] [nvarchar](max) NULL,
	[NonVATItem] [nvarchar](max) NULL,
	[recId] [int] NOT NULL
)

DECLARE @recId int, @itemID int, @masterName nvarchar(255), @desc nvarchar(255), @brand nvarchar(255),
@department nvarchar(255), @subdepartment nvarchar(255), @unitSize nvarchar(255), 
@shrink int, @case int, @nonVATItem nvarchar(1);
SET rowcount 0

BULK INSERT #masters
--change path to the file to be imported
FROM 'D:\EvolutionSoft\tickets\REAP-1\REAP-144\TestImport_ProductMasters.csv'
WITH
(
  FIRSTROW = 2,
  DATAFILETYPE='widechar', -- UTF-16
  FIELDTERMINATOR = ';',
  ROWTERMINATOR = '\n',
  TABLOCK,
  KEEPNULLS -- Treat empty fields as NULLs.
)

SET rowcount 1
select @recId = recId, 
@masterName = MasterName,
@desc = Description,
@brand = Brand,
@department = Department,
@subdepartment = Subdepartment,
@unitSize = UnitSize, 
@shrink = ShrinkQuantity, 
@case = CaseQuantity,
@nonVATItem = NonVATItem
from #masters;

WHILE @@rowcount <> 0
BEGIN
    SET rowcount 0
	DELETE #masters WHERE recId = @recId
	DECLARE @itemQuery nvarchar(max), @query nvarchar(max);

	SET @masterName = REPLACE(@masterName, CHAR(39), CONCAT(CHAR(39), CHAR(39)));
	SET @desc = REPLACE(@desc, CHAR(39), CONCAT(CHAR(39), CHAR(39)));
	SET @unitSize = REPLACE(@unitSize, CHAR(39), CONCAT(CHAR(39), CHAR(39)));
	SET @brand= REPLACE(@brand, CHAR(39), CONCAT(CHAR(39), CHAR(39)));

	PRINT('MasterName=|'+@masterName  +'|..');

	SET @itemQuery = 'INSERT INTO [dbo].[pal_SurfaceItem]
           ([surfaceId]
           ,[dateCreated]
           ,[isActive]
           ,[createdBy]
           ,[updatedBy]
           ,[dateUpdated]
           ,[isDeleted]
           ,[isWizardCompleted]
           ,[lastTabCompleted]
           ,[userLink]
		   ,[surfaceLinkItems])
     VALUES
           (3098
           ,'+CHAR(39) +'2020-06-15 12:00:00.000'+CHAR(39) +'
           ,1
           ,217
           ,0
           ,'+CHAR(39) +'1900-01-01 00:00:00.000'+CHAR(39) +'
           ,0
           ,0
           ,0
           ,0
		   ,'+CHAR(39)+''+CHAR(39)+')';
	EXEC(@itemQuery);
	SET @itemID = @@IDENTITY;

	SET @query = 'INSERT INTO [dbo].[publ_ProductMasters]
           ([itemID]
           ,[isActive]
           ,[surfaceId]
           ,[ProductMasters_ProductMasterDetails_ProductDescription]
           ,[ProductMasters_ProductMasterFreightDetails_VolumetricFactor]
           ,[ProductMasters_ProductMasterDetails_UnitSize]
           ,[ProductMasters_ProductMasterFreightDetails_UnitLength]
           ,[ProductMasters_ProductMasterFreightDetails_UnitWidth]
           ,[ProductMasters_ProductMasterDetails_ProductMasterName]
           ,[ProductMasters_ProductMasterDetails_Brand]
           ,[ProductMasters_ProductMasterFreightDetails_UnitHeight]
           ,[ProductMasters_ProductMasterFreightDetails_UnitResult]
           ,[ProductMasters_ProductMasterDetails_ShrinkQuantity]
           ,[ProductMasters_ProductMasterDetails_CaseQuantity]
           ,[ProductMasters_ProductMasterFreightDetails_UnitVolumetricWeight]
           ,[ProductMasters_ProductMasterFreightDetails_UnitActualWeight]
           ,[ProductMasters_ProductMasterDetails_Department]
           ,[ProductMasters_ProductMasterDetails_Subdepartment]
           ,[ProductMasters_ProductMasterFreightDetails_UnitFreightWeight]
           ,[ProductMasters_ProductMasterDetails_LastUpdated]
           ,[ProductMasters_ProductMasterFreightDetails_ShrinkLength]
           ,[ProductMasters_ProductMasterFreightDetails_ShrinkWidth]
           ,[ProductMasters_ProductMasterDetails_UpdatedBy]
           ,[ProductMasters_ProductMasterFreightDetails_ShrinkHeight]
           ,[ProductMasters_ProductMasterFreightDetails_ShrinkResult]
           ,[ProductMasters_ProductMasterFreightDetails_ShrinkVolumetricWeight]
           ,[ProductMasters_ProductMasterFreightDetails_ShrinkActualWeight]
           ,[ProductMasters_ProductMasterFreightDetails_ShrinkFreightWeight]
           ,[ProductMasters_ProductMasterFreightDetails_CaseLength]
           ,[ProductMasters_ProductMasterFreightDetails_CaseWidth]
           ,[ProductMasters_ProductMasterFreightDetails_CaseHeight]
           ,[ProductMasters_ProductMasterFreightDetails_CaseResult]
           ,[ProductMasters_ProductMasterFreightDetails_CaseVolumetricWeight]
           ,[ProductMasters_ProductMasterFreightDetails_CaseActualWeight]
           ,[ProductMasters_ProductMasterFreightDetails_CaseFreightWeight]
		   ,[ProductMasters_ProductMasterDetails_NonVATItem])
     VALUES
           ('+CAST(@itemID as varchar)+'
           ,1
           ,3098
           ,'+CHAR(39)+@desc+CHAR(39)+'
           ,0
           ,'+CHAR(39)+@unitSize+CHAR(39)+'
           ,0
           ,0
           ,'+CHAR(39)+@masterName+CHAR(39)+'
           ,'+CHAR(39)+@brand+CHAR(39)+'
           ,0
           ,0
           ,'+CAST(@shrink as varchar)+'
           ,'+CAST(@case as varchar)+'
           ,0
           ,0
           ,'+CHAR(39)+@department+CHAR(39)+'
           ,'+CHAR(39)+@subdepartment+CHAR(39)+'
           ,0
           ,'+CHAR(39) +'2020-06-15 12:00:00.000'+CHAR(39) +'
           ,0
           ,0
           ,' + CHAR(39) + 'Dylan Reevell' + CHAR(39) +'
           ,0
           ,0
           ,0
           ,0
           ,0
           ,0
           ,0
           ,0
           ,0
           ,0
           ,0
           ,0
		   ,'+@nonVATItem+')';
	
	EXEC(@query);
	
	SET rowcount 1
	select @recId = recId, 
	@masterName = MasterName,
	@desc = Description,
	@brand = Brand,
	@department = Department,
	@subdepartment = Subdepartment,
	@unitSize = UnitSize, 
	@shrink = ShrinkQuantity, 
	@case = CaseQuantity,
	@nonVATItem = NonVATItem
	from #masters;

END
set rowcount 0;
DROP TABLE #masters;
