select * from Stockitem where (code not in (select stockcode from MSEStockItem))

-- This script ensures that we have data in the following tables if
-- data already exists in the core table they are related to.
---- MseProductGroup
-- MseStockItem
-- MseWarehouse
-- MseWarehouseItem
--************************ MseProductGroup ************************
-- Declare variables to hold source data
DECLARE @SourceID bigint
-- Declare and open cursor
DECLARE dataCursor CURSOR FOR SELECT ProductGroupID FROM ProductGroup WHERE (ProductGroupID NOT IN (SELECT ProductGroupID FROM MseProductGroup))
OPEN dataCursor 
-- Process data
FETCH Next FROM dataCursor INTO @SourceID
WHILE @@FETCH_STATUS = 0
BEGIN	
-- Get next counter and then increment it
	DECLARE @Counter bigint	SET @Counter = (SELECT TOP 1 NextValue FROM Counter)	UPDATE Counter SET NextValue = NextValue + 1	
-- Insert row into our parallel table
	INSERT INTO MseProductGroup (MseProductGroupID, ProductGroupID, MseContactID, UseDemandWarehouse, UseWOComponentWarehouse)
	VALUES (@Counter, @SourceID, null, 0, 0)		
-- Get next row	
FETCH Next FROM dataCursor INTO @SourceID
END
-- Close and deallocate cursor
CLOSE dataCursor
DEALLOCATE dataCursor
GO
--************************ MseStockItem ************************
-- Declare variables to hold source data
DECLARE @SourceCode varchar(255)
DECLARE @BomItemTypeID bigint
-- Declare and open cursor
DECLARE dataCursor CURSOR FOR SELECT Code, BomItemTypeID FROM StockItem WHERE (Code NOT IN (SELECT StockCode FROM MseStockItem))
OPEN dataCursor 
-- Process data
FETCH Next FROM dataCursor INTO @SourceCode, @BomItemTypeID
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Get next counter and then increment it
	DECLARE @Counter bigint	SET @Counter = (SELECT TOP 1 NextValue FROM Counter)	UPDATE Counter SET NextValue = NextValue + 1
	-- Insert row into our parallel table
	INSERT INTO MseStockItem (MseStockItemID, StockCode, AggregateDays, WorksOrderBatchMinQty, WorksOrderBatchMaxQty,
		CanCancelWorksOrders, CanCancelPurchaseOrders, MRPReplenishmentRulesTypeID, MRPReplenishmentMultipleValue,
		CanAmendPOReceiptAllocation, Linked, UseDemandWarehouse, UseWOCompletionWarehouse, ApplyReorderLevelAfterMaximum, ReplenishmentHorizonDays,
		BuiltBoughtDefaultMake, AdditionalDescription2, LeadTime, StdCost, Conversion, BoughtInUnit, BoughtInDesc, ProcessUnit,
		ProcessDesc, AutoUpdateSuppliers, Quarantine, MaximumStockLevel, ShelfLifeNo, ShelfLifeInterval, MaximumBatchSize,
		BuyerCode, MakeItem, OrderingMethod, OrderMultiple, ScrapPercent, StockConversionRound, BulkIssue, BomItemType, AdditionalDescription1,
		CostFreeze, MsmCostHeadingID)
	VALUES (@Counter, @SourceCode, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 1, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, @BomItemTypeID, '', 0, 1)
	-- Get next row
	FETCH Next FROM dataCursor INTO @SourceCode, @BomItemTypeID
END
-- Close and deallocate cursor
CLOSE dataCursor
DEALLOCATE dataCursor
GO
--************************ MseWarehouse ************************
-- Declare variables to hold source data
DECLARE @SourceID bigint
-- Declare and open cursor
DECLARE dataCursor CURSOR FOR SELECT WarehouseID FROM Warehouse WHERE (WarehouseTypeID NOT IN (2,3)) AND (WarehouseID NOT IN (SELECT WarehouseID FROM MseWarehouse))
OPEN dataCursor 
-- Process data
FETCH Next FROM dataCursor INTO @SourceID
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Get next counter and then increment it
	DECLARE @Counter bigint	SET @Counter = (SELECT TOP 1 NextValue FROM Counter)	UPDATE Counter SET NextValue = NextValue + 1
	-- Insert row into our parallel table
	INSERT INTO MseWarehouse (MseWarehouseID, WarehouseID, IsComponentSource, MseContactID)
	VALUES (@Counter, @SourceID, 1, null)
	-- Get next row
	FETCH Next FROM dataCursor INTO @SourceID
END
-- Close and deallocate cursor
CLOSE dataCursor
DEALLOCATE dataCursor
GO
--************************ MseWarehouseItem ************************
DECLARE @Counter bigint SET @Counter = (SELECT TOP 1 NextValue FROM Counter)
UPDATE Counter SET NextValue = NextValue + 1
INSERT INTO MseWarehouseItem(MseWarehouseItemID, WarehouseItemID, IsDefaultWorksOrderWarehouse)
	SELECT @Counter + RANK() OVER (ORDER BY WarehouseItemID), WarehouseItemID, 0 	FROM WarehouseItem
 	WHERE (WarehouseItemID NOT IN (SELECT WarehouseItemID FROM MseWarehouseItem))
IF (SELECT MAX(MseWarehouseItemID) FROM MseWarehouseItem) > @Counter
	UPDATE Counter SET NextValue = (SELECT MAX(MseWarehouseItemID) + 1 FROM MseWarehouseItem)
DECLARE itemCursor CURSOR FOR SELECT DISTINCT ItemID FROM WarehouseItem
DECLARE @ItemID bigint
OPEN itemCursor
FETCH Next FROM itemCursor INTO @ItemID
WHILE @@FETCH_STATUS = 0
BEGIN
	IF NOT EXISTS(SELECT TOP 1 * FROM MseWarehouseItem, WarehouseItem WHERE MseWarehouseItem.WarehouseItemID = WarehouseItem.WarehouseItemID AND WarehouseItem.ItemID = @ItemID AND MseWarehouseItem.IsDefaultWorksOrderWarehouse = 1)
	BEGIN
		UPDATE MseWarehouseItem SET IsDefaultWorksOrderWarehouse = 1
		WHERE WarehouseItemID = (SELECT TOP 1 WarehouseItemID FROM WarehouseItem WHERE ItemID = @ItemID)
	END
	FETCH Next FROM itemCursor INTO @ItemID
END
-- Close and deallocate cursor
CLOSE itemCursor
DEALLOCATE itemCursor
GO

-- Synchronises the contents of the MseStockItem & MFGStockItem tables.
-- First make sure we have entries to copy in the MseStockItem table.
IF EXISTS(SELECT * FROM MseStockItem)
BEGIN	
-- Now make sure that we have an MFGStockItem table.
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MFGStockItem]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		-- Remove any existing MFGStockItem entries.
		DELETE FROM MFGStockItem
		-- Copy all the rows from MseStockItem into MFGStockItem
		INSERT INTO MFGStockItem(MFGStockItemID, StockItemID, AggregateDays, WorksOrderBatchMinQty, WorksOrderBatchMaxQty, MFGContactID, CanCancelWorksOrders, CanCancelPurchaseOrders, MRPReplenishmentRulesTypeID, MRPReplenishmentMultipleValue, CanAmendPOReceiptAllocation, Linked, UseDemandWarehouse, UseWOCompletionWarehouse, ApplyReorderLevelAfterMaximum, ReplenishmentHorizonDays, BuiltBoughtDefaultMake)
		SELECT MseStockItemID, s.ItemID, AggregateDays, WorksOrderBatchMinQty, WorksOrderBatchMaxQty, MseContactID,  CanCancelWorksOrders, CanCancelPurchaseOrders, MRPReplenishmentRulesTypeID, MRPReplenishmentMultipleValue, CanAmendPOReceiptAllocation, Linked, UseDemandWarehouse, UseWOCompletionWarehouse, ApplyReorderLevelAfterMaximum, ReplenishmentHorizonDays, BuiltBoughtDefaultMake FROM MseStockItem m, StockItem s	    WHERE m.StockCode = s.Code;
			END
END
GO
-- Synchronises the contents of the MseWarehouseItem & MFGWarehouseItem tables.
-- First make sure we have entries to copy in the MseWarehouseItem table.
IF EXISTS(SELECT * FROM MseWarehouseItem)
BEGIN
	-- Now make sure that we have an MFGWarehouseItem table.
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MFGWarehouseItem]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		-- Remove any existing MFGWarehouseItem entries.
		DELETE FROM MFGWarehouseItem
		-- Copy all the rows from MseWarehouseItem into MFGWarehouseItem
		INSERT INTO MFGWarehouseItem(MFGWarehouseItemID, WarehouseItemID, IsDefaultWorksOrderWarehouse )
		SELECT MseWarehouseItemID, WarehouseItemID, IsDefaultWorksOrderWarehouse FROM MseWarehouseItem;
			END
END
GO
-- Synchronises the contents of the MseProductGroup & MFGProductGroup tables.
-- First make sure we have entries to copy in the MseProductGroup table.
IF EXISTS(SELECT * FROM MseProductGroup)
BEGIN
	-- Now make sure that we have an MFGProductGroup table.
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MFGProductGroup]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		-- Remove any existing MFGProductGroup entries.
		DELETE FROM MFGProductGroup
		-- Copy all the rows from MseProductGroup into MFGProductGroup
		INSERT INTO MFGProductGroup(MFGProductGroupID, ProductGroupID, MFGContactID, UseDemandWarehouse, UseWOComponentWarehouse)
		SELECT MseProductGroupID, ProductGroupID, MseContactID, UseDemandWarehouse, UseWOComponentWarehouse FROM MseProductGroup;
			END
END
GO
-- Synchronises the contents of the MseWarehouse & MFGWarehouse tables.
-- First make sure we have entries to copy in the MseWarehouse table.
IF EXISTS(SELECT * FROM MseWarehouse)
BEGIN
	-- Now make sure that we have an MFGWarehouse table.
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MFGWarehouse]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		-- Remove any existing MFGWarehouse entries.
		DELETE FROM MFGWarehouse
		-- Copy all the rows from MseWarehouse into MFGWarehouse
		INSERT INTO MFGWarehouse(MFGWarehouseID, WarehouseID, IsComponentSource, MFGContactID)
		SELECT MseWarehouseID, WarehouseID, IsComponentSource, MseContactID FROM MseWarehouse;
			END
END
GO
-- Remove any duplicate entries fromt the MFGComponentWarehouses table.
DELETE FROM MFGComponentWarehouses
WHERE MfgComponentWarehousesID IN(
	SELECT MfgComponentWarehousesID FROM
	(
		SELECT     (ROW_NUMBER() OVER(PARTITION BY ParentWarehouseID, ChildWarehouseID ORDER BY MfgComponentWarehousesID)) AS ROWID,
		 MfgComponentWarehousesID, ParentWarehouseID, ChildWarehouseID
		FROM         MFGComponentWarehouses
	) A WHERE ROWID != 1)
	GO