USE Resources01_Dat
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Number_of_Repairs' )
ALTER TABLE [dbo].[tblDts] ADD [Number_of_Repairs] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Is_Lemon' )
ALTER TABLE [dbo].[tblDts] ADD [Is_Lemon] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Assignee_Grade_Level' )
ALTER TABLE [dbo].[tblDts] ADD [Assignee_Grade_Level] nvarchar (255) NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Assignee_ID' )
ALTER TABLE [dbo].[tblDts] ADD [Assignee_ID] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Assignee_Homeroom_Teacher' )
ALTER TABLE [dbo].[tblDts] ADD [Assignee_Homeroom_Teacher] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Assignee_Location' )
ALTER TABLE [dbo].[tblDts] ADD [Assignee_Location] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Assignee_Photo' )
ALTER TABLE [dbo].[tblDts] ADD [Assignee_Photo] nvarchar (255) NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Assignee_Category' )
ALTER TABLE [dbo].[tblDts] ADD [Assignee_Category] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Subcategory' )
ALTER TABLE [dbo].[tblDts] ADD [Subcategory] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Approval_Path' )
ALTER TABLE [dbo].[tblDts] ADD [Approval_Path] int NULL
GO


/*** DELETE LESS PRIORITY INDEXES ****/
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Access_Level' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Access_Level] 
GO

IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Account_Type' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Account_Type] 
GO

IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Active_Directory_Groups' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Active_Directory_Groups] 
GO

IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Allocation_Rule' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Allocation_Rule] 
GO

IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Allocation_Units' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Allocation_Units] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Allow_Multiple_Instances_Per_Role_or_Employee' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Allow_Multiple_Instances_Per_Role_or_Employee] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Approval_Needed_For' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Approval_Needed_For] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Asset_Expires_' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Asset_Expires_] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Asset_Life_Cycle_Stage' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Asset_Life_Cycle_Stage] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Asset_Request_Owner' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Asset_Request_Owner] 
GO


IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Asset_Request_Work_Team' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Asset_Request_Work_Team] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Assigned_to_Engineer' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Assigned_to_Engineer] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Authorization_Required' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Authorization_Required] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Auto_Create_License_Record' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Auto_Create_License_Record] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Auto_Import_Installations' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Auto_Import_Installations] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Base_Resource' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Base_Resource] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Can_Be_Requested' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Can_Be_Requested] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Deliver_To' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Deliver_To] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Depreciation_Scheme' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Depreciation_Scheme] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Device_will_contain_Confidential_Data' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Device_will_contain_Confidential_Data] 
GO


IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Environment' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Environment] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Estimated$Effort' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Estimated$Effort] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Expected_Life' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Expected_Life] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Fileshare_Department' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Fileshare_Department] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Folder_Security' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Folder_Security] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_For_international_travel' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_For_international_travel] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_For_Travel' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_For_Travel] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Has_An_Associated_Cost_' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Has_An_Associated_Cost_] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Has_Been_Utilized' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Has_Been_Utilized] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Has_Child_Asset_Groups' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Has_Child_Asset_Groups] 
GO


IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Is_Bulk_Instance' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Is_Bulk_Instance] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Is_Sensitive' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Is_Sensitive] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Item_Level' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Item_Level] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Managed' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Managed] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Mandatory' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Mandatory] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_nControlledByParent' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_nControlledByParent] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Needed_for_Device' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Needed_for_Device] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Pool_Responsible' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Pool_Responsible] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Purchased_From' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Purchased_From] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Rating' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Rating] 
GO


IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Related_Bulk_Asset' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Related_Bulk_Asset] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Relevant_to_Data_Protection' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Relevant_to_Data_Protection] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Request_Priority' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Request_Priority] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Resource_Type' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Resource_Type] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Return_to_Storage_Location' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Return_to_Storage_Location] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Sample_Data' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Sample_Data]
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Service_Contractor' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Service_Contractor] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Software_Category' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Software_Category] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Supplier_Seller' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Supplier_Seller] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Track_As' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Track_As] 
GO


IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Training' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Training] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Training_is_Requested' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Training_is_Requested] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Training_Level' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Training_Level] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_tShowInSummary' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_tShowInSummary] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Visible' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Visible] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_VM_Platform' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_VM_Platform] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_VM_Type' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_VM_Type] 
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE name='IN_tblDts_Warranty_Status' AND object_id = OBJECT_ID('Schema.tblDts'))
DROP INDEX [tblDts].[IN_tblDts_Warranty_Status] 
GO


/*** ADD CONTAINER FIELD AND CHROMEBOOK CONTAINER ASSET TYPE ****/
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Pool_Responsible' )
	EXEC sp_rename 'tblDts.Pool_Responsible', 'Container', 'COLUMN';
GO

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Pool' )
	EXEC sp_rename 'tbldts.Pool', 'Container', 'COLUMN';

GO

IF NOT EXISTS(SELECT * FROM [tblDts] WHERE  [nID]=100 )
	INSERT [dbo].[tblDts] ([nID], [nRevisionNumber], [tShowInSummary], [nUserID], [dAssignedDate], [dAssignedTime], [dClosedDate], 
		[dClosedTime], [dLastUpdateDate], [dSubmittedDate], [dSubmittedTime], [dUpdateTime], [mDetailedDescription], [nOriginatorID], 
		[nPriorityID], [nSubmitterID], [nSubstateID], [tBriefDescription], [nStateID], [Activity], [Activity$Log], [Estimated$Effort], 
		[Planned$Start$Date], [Planned$End$Date], [ComputerName], [nControlledByParent], [nSource], [Purchased_Date], [Comments], 
		[Resource_Will_Expire_On], [Mandatory], [Provisioned], [Has_Been_Utilized], [Is_Sensitive], [nUsageType], [Estimated_Duration], 
		[Historical_Duration], [Status], [Estimated_Start_Time], [Estimated_Closure_Time], [Access_Level], [Training], [Vendor], [Has_An_Associated_Cost_], 
		[Printer_Name], [Active_Directory_Groups], [Allow_Multiple_Instances_Per_Role_or_Employee], [Open_Role_Allocation], 
		[Complete_Provisioning_By], [Training_Level], [Share_Name], [Access_Code_Location], [Fileshare_Department], [Brand], [Model],
		[Serial_Number], [Barcode_Tag], [Asset_Tag], [Supplier_Seller], [Unit_Price], [Service_Contractor], [Delivery_Date], 
		[Depreciation_Scheme], [Warranty_Until], [Expected_Life], [Salvage_Value], [Invoice_Reference], [Rest_Value], [Retired_Date], 
		[Asset_Sub_status], [Quantity], [Asset_Location], [Total_Assets], [Assets_in_Use], [Assets_Remaining], [Item_Level], 
		[UNSPSC_Code], [ASIN], [Manufacturer_ID], [Rating], [Parent], [Has_Child_Asset_Groups], [Bulk_Assets], [Managed], [CPU], 
		[Memory], [SN], [OS_Name], [OS_Version], [Manufacturer], [PC_Model], [tSID], [License_Key], [Short_Name], [Image_Version], 
		[Key_Name], [Base_Board_Manufacturer], [Base_Board_Product], [Base_Board_Version], [Bios_Major_Release], [Bios_Minor_Release], 
		[Bios_Release_Date], [Bios_Vendor], [Bios_Version], [EC_Firmware_Major_Release], [EC_Firmware_Minor_Release], [System_Family],
		[System_Manufacturer], [System_Product_Name], [System_SKU], [System_Version], [Responsible], [Import_Source], [Import_Source_Details], [CPU_Speed], [Domain_Name], [IP_Address], [Network_Card], [Subnet_Mask], [MAC_Address], [Software_Publisher], [Accessible_To], [Warranty_Description], [Warranty_Start_Date], [Warranty_Status], [Warranty_ID], [Asset_Expires_], [Folder_Security], [Account_Type], [Account_Name], [VM_Platform], [VM_Type], [Host_Server], [VM_URL], [Source_Type], [Source_Name], [Import_Alias_1], [Import_Alias_2], [Import_Alias_3], [Last_Imported_Date], [Last_Imported_Time], [Asset_Request_Level_1_Approvers], [Asset_Request_Level_2_Approvers], [Asset_Request_Level_3_Approvers], [Asset_Request_Owner], [Asset_Request_Work_Team], [Minimum_Provisioning_Time], [Request_Priority], [Track_As], [Minimum_Quantity], [Current_Quantity], [Maximum_Quantity], [Need_to_Order_Quantity], [Allocation_Units], [Total_Allocated], [Total_Purchased], [Total_Costs_Over_Time], [Average_Total_Cost_Per_Unit], [Amount_to_Order], [Allocated_to_Department], [Allocated_to_Unit_Division], [Allocated_to_Project], [Can_Be_Requested], [Visible], [Software_Discovery_Mappings], [CPU_Count], [CPU_Cores_Count], [Primary_Owner], [Source_Timestamp], [Last_Imported_Timestamp], [Top_Console_User], [Visible_Request_Fields], [Request_Required_By_Date], [Request_Required_By_Time], [Number_Requested], [For_international_travel], [Requested_Until], [Request_Warning_Message], [Need_an_RSA_token], [Deliver_To], [Special_Instructions], [Laptop_Accessories_Needed], [For_Travel], [Litigation_Software], [Content_Loaded_on_Device], [Matter_Number], [Matter_Name], [Reason_for_Request], [Training_is_Requested], [Device_will_contain_Confidential_Data], [Needed_for_Device], [No_Approval_Needed_Level_1], [No_Approval_Needed_Level_2], [No_Approval_Needed_Level_3], [SIM_Card_Number], [IMEI], [Phone_Number], [Auto_Import_Installations], [Associated_Cost], [Approval_Needed_For], [Allocation_Rule], [Requested_Until_Time], [Related_License], [Location], [Sample_Data], [Environment], [Authorization_Required], [Application], [Access_Level_Is_Required_], [Training_Is_Required_], [ComputerName_Is_Required_], [Application_Is_Required_], [Vendor_Is_Required_], [Printer_Name_Is_Required_], [Active_Directory_Groups_Is_Required_], [Is_Missing_Required_Value], [Training_Level_Is_Required_], [Share_Name_Is_Required_], [Access_Code_Location_Is_Required_], [Room_Number], [Room_Number_Is_Required_], [Fileshare_Department_Is_Required_], [Invoice_Date], [Asset_Status], [PO_Number], [Purchased_From], [Contract_Duration__Years_], [Serial_Number_Is_Required_], [Auto_Create_License_Record], [Level_1], [Level_2], [Container], [Key], [Version], [Edition], [Allocated_to_Device___Computer], [dActualIRTDate], [dActualIRTTime], [ComputerChoiceList], [dTargetClosureTime], [dTargetIRT], [nAssociatedSLA], [Minimum_Approvers_Required], [Needs_Attention___Not_Seen_Recently], [Related_Bulk_Asset], [Asset_Life_Cycle_Stage], [Related_HelpDesk_Issues], [Key_Discovery___Application_Name], [Borrowable], [Software_Category], [Recurring_Costs], [Custom_Approvers], [Is_Bulk_Instance], [Reference_ID], [Family], [Source_Creation_Date], [View_Details], [Send_For_Repair], [Return_to_Storage_Location], [Check_In], [nEscalationLevel], [nServiceTypeID], [dEscalationTimestamp], [nEscalationOn], [nSeverityID], [nCumulativePausedTime], [LastAudit], [Check_In_Asset], [Check_Out_Asset], [Relevant_to_Data_Protection], [To_Be_Deleted], [Lost_Date], [Abbreviation], [Asset_Type_Count], [Number_of_Repairs], [Is_Lemon], [Assignee_Grade_Level], [Assignee_ID], [Assignee_Homeroom_Teacher], [Assignee_Location], [Assignee_Photo], [Assignee_Category]) 

	VALUES (100, 7, N'-1', 0, NULL, NULL, NULL, NULL, CAST(N'2018-07-11T00:00:00.000' AS DateTime), 
		CAST(N'2018-07-08T00:00:00.000' AS DateTime), CAST(N'1899-12-30T16:05:09.000' AS DateTime), 
		CAST(N'1899-12-30T09:30:50.000' AS DateTime), NULL, 2, NULL, 2, NULL, N'Chromebook Container', 1, NULL, NULL, 
		NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, NULL, NULL, NULL, NULL, NULL, 
		NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
		NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 3, 3, 0, 3, NULL, NULL, NULL, NULL, NULL, 2, NULL, NULL, 
		NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
		NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
		NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
		NULL, NULL, NULL, NULL, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, 1, NULL, NULL, 
		NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
		NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0.0000, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
		NULL, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, 0, NULL, 314, NULL, NULL, NULL, NULL, NULL, NULL, 
		NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
		NULL, 0, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

GO

IF NOT EXISTS(SELECT * FROM [tblDts] WHERE  [nID]=496 )
	INSERT [dbo].[tblDts] ([nID], [nRevisionNumber], [tShowInSummary], [nUserID], [dAssignedDate], [dAssignedTime], [dClosedDate], [dClosedTime], [dLastUpdateDate], [dSubmittedDate], [dSubmittedTime], [dUpdateTime], [mDetailedDescription], [nOriginatorID], [nPriorityID], [nSubmitterID], [nSubstateID], [tBriefDescription], [nStateID], [Activity], [Activity$Log], [Estimated$Effort], [Planned$Start$Date], [Planned$End$Date], [ComputerName], [nControlledByParent], [nSource], [Purchased_Date], [Comments], [Resource_Will_Expire_On], [Mandatory], [Provisioned], [Has_Been_Utilized], [Is_Sensitive], [nUsageType], [Estimated_Duration], [Historical_Duration], [Status], [Estimated_Start_Time], [Estimated_Closure_Time], [Access_Level], [Training], [Vendor], [Has_An_Associated_Cost_], [Printer_Name], [Active_Directory_Groups], [Allow_Multiple_Instances_Per_Role_or_Employee], [Open_Role_Allocation], [Complete_Provisioning_By], [Training_Level], [Share_Name], [Access_Code_Location], [Fileshare_Department], [Brand], [Model], [Serial_Number], [Barcode_Tag], [Asset_Tag], [Supplier_Seller], [Unit_Price], [Service_Contractor], [Delivery_Date], [Depreciation_Scheme], [Warranty_Until], [Expected_Life], [Salvage_Value], [Invoice_Reference], [Rest_Value], [Retired_Date], [Asset_Sub_status], [Quantity], [Asset_Location], [Total_Assets], [Assets_in_Use], [Assets_Remaining], [Item_Level], [UNSPSC_Code], [ASIN], [Manufacturer_ID], [Rating], [Parent], [Has_Child_Asset_Groups], [Bulk_Assets], [Managed], [CPU], [Memory], [SN], [OS_Name], [OS_Version], [Manufacturer], [PC_Model], [tSID], [License_Key], [Short_Name], [Image_Version], [Key_Name], [Base_Board_Manufacturer], [Base_Board_Product], [Base_Board_Version], [Bios_Major_Release], [Bios_Minor_Release], [Bios_Release_Date], [Bios_Vendor], [Bios_Version], [EC_Firmware_Major_Release], [EC_Firmware_Minor_Release], [System_Family], [System_Manufacturer], [System_Product_Name], [System_SKU], [System_Version], [Responsible], [Import_Source], [Import_Source_Details], [CPU_Speed], [Domain_Name], [IP_Address], [Network_Card], [Subnet_Mask], [MAC_Address], [Software_Publisher], [Accessible_To], [Warranty_Description], [Warranty_Start_Date], [Warranty_Status], [Warranty_ID], [Asset_Expires_], [Folder_Security], [Account_Type], [Account_Name], [VM_Platform], [VM_Type], [Host_Server], [VM_URL], [Source_Type], [Source_Name], [Import_Alias_1], [Import_Alias_2], [Import_Alias_3], [Last_Imported_Date], [Last_Imported_Time], [Asset_Request_Level_1_Approvers], [Asset_Request_Level_2_Approvers], [Asset_Request_Level_3_Approvers], [Asset_Request_Owner], [Asset_Request_Work_Team], [Minimum_Provisioning_Time], [Request_Priority], [Track_As], [Minimum_Quantity], [Current_Quantity], [Maximum_Quantity], [Need_to_Order_Quantity], [Allocation_Units], [Total_Allocated], [Total_Purchased], [Total_Costs_Over_Time], [Average_Total_Cost_Per_Unit], [Amount_to_Order], [Allocated_to_Department], [Allocated_to_Unit_Division], [Allocated_to_Project], [Can_Be_Requested], [Visible], [Software_Discovery_Mappings], [CPU_Count], [CPU_Cores_Count], [Primary_Owner], [Source_Timestamp], [Last_Imported_Timestamp], [Top_Console_User], [Visible_Request_Fields], [Request_Required_By_Date], [Request_Required_By_Time], [Number_Requested], [For_international_travel], [Requested_Until], [Request_Warning_Message], [Need_an_RSA_token], [Deliver_To], [Special_Instructions], [Laptop_Accessories_Needed], [For_Travel], [Litigation_Software], [Content_Loaded_on_Device], [Matter_Number], [Matter_Name], [Reason_for_Request], [Training_is_Requested], [Device_will_contain_Confidential_Data], [Needed_for_Device], [No_Approval_Needed_Level_1], [No_Approval_Needed_Level_2], [No_Approval_Needed_Level_3], [SIM_Card_Number], [IMEI], [Phone_Number], [Auto_Import_Installations], [Associated_Cost], [Approval_Needed_For], [Allocation_Rule], [Requested_Until_Time], [Related_License], [Location], [Sample_Data], [Environment], [Authorization_Required], [Application], [Access_Level_Is_Required_], [Training_Is_Required_], [ComputerName_Is_Required_], [Application_Is_Required_], [Vendor_Is_Required_], [Printer_Name_Is_Required_], [Active_Directory_Groups_Is_Required_], [Is_Missing_Required_Value], [Training_Level_Is_Required_], [Share_Name_Is_Required_], [Access_Code_Location_Is_Required_], [Room_Number], [Room_Number_Is_Required_], [Fileshare_Department_Is_Required_], [Invoice_Date], [Asset_Status], [PO_Number], [Purchased_From], [Contract_Duration__Years_], [Serial_Number_Is_Required_], [Auto_Create_License_Record], [Level_1], [Level_2], [Container], [Key], [Version], [Edition], [Allocated_to_Device___Computer], [dActualIRTDate], [dActualIRTTime], [ComputerChoiceList], [dTargetClosureTime], [dTargetIRT], [nAssociatedSLA], [Minimum_Approvers_Required], [Needs_Attention___Not_Seen_Recently], [Related_Bulk_Asset], [Asset_Life_Cycle_Stage], [Related_HelpDesk_Issues], [Key_Discovery___Application_Name], [Borrowable], [Software_Category], [Recurring_Costs], [Custom_Approvers], [Is_Bulk_Instance], [Reference_ID], [Family], [Source_Creation_Date], [View_Details], [Send_For_Repair], [Return_to_Storage_Location], [Check_In], [nEscalationLevel], [nServiceTypeID], [dEscalationTimestamp], [nEscalationOn], [nSeverityID], [nCumulativePausedTime], [LastAudit], [Check_In_Asset], [Check_Out_Asset], [Relevant_to_Data_Protection], [To_Be_Deleted], [Lost_Date], [Abbreviation]) 
	VALUES (496, 7, N'-1', 2, CAST(0x0000A98E00000000 AS DateTime), CAST(0xFFFFFFFE0007209C AS DateTime), NULL, NULL, CAST(0x0000A98E00000000 AS DateTime), CAST(0x0000A98E00000000 AS DateTime), CAST(0xFFFFFFFE00028B90 AS DateTime), CAST(0xFFFFFFFE000722F4 AS DateTime), NULL, 2, NULL, 2, NULL, N'CBCO00002', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'CBCO00002', NULL, 0.0000, NULL, NULL, NULL, NULL, NULL, 0.0000, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, 0, NULL, 314, 100, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0.0000, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL )

GO

IF NOT EXISTS(SELECT * FROM [tblDts] WHERE  [nID]=497 )
	INSERT [dbo].[tblDts] ([nID], [nRevisionNumber], [tShowInSummary], [nUserID], [dAssignedDate], [dAssignedTime], [dClosedDate], [dClosedTime], [dLastUpdateDate], [dSubmittedDate], [dSubmittedTime], [dUpdateTime], [mDetailedDescription], [nOriginatorID], [nPriorityID], [nSubmitterID], [nSubstateID], [tBriefDescription], [nStateID], [Activity], [Activity$Log], [Estimated$Effort], [Planned$Start$Date], [Planned$End$Date], [ComputerName], [nControlledByParent], [nSource], [Purchased_Date], [Comments], [Resource_Will_Expire_On], [Mandatory], [Provisioned], [Has_Been_Utilized], [Is_Sensitive], [nUsageType], [Estimated_Duration], [Historical_Duration], [Status], [Estimated_Start_Time], [Estimated_Closure_Time], [Access_Level], [Training], [Vendor], [Has_An_Associated_Cost_], [Printer_Name], [Active_Directory_Groups], [Allow_Multiple_Instances_Per_Role_or_Employee], [Open_Role_Allocation], [Complete_Provisioning_By], [Training_Level], [Share_Name], [Access_Code_Location], [Fileshare_Department], [Brand], [Model], [Serial_Number], [Barcode_Tag], [Asset_Tag], [Supplier_Seller], [Unit_Price], [Service_Contractor], [Delivery_Date], [Depreciation_Scheme], [Warranty_Until], [Expected_Life], [Salvage_Value], [Invoice_Reference], [Rest_Value], [Retired_Date], [Asset_Sub_status], [Quantity], [Asset_Location], [Total_Assets], [Assets_in_Use], [Assets_Remaining], [Item_Level], [UNSPSC_Code], [ASIN], [Manufacturer_ID], [Rating], [Parent], [Has_Child_Asset_Groups], [Bulk_Assets], [Managed], [CPU], [Memory], [SN], [OS_Name], [OS_Version], [Manufacturer], [PC_Model], [tSID], [License_Key], [Short_Name], [Image_Version], [Key_Name], [Base_Board_Manufacturer], [Base_Board_Product], [Base_Board_Version], [Bios_Major_Release], [Bios_Minor_Release], [Bios_Release_Date], [Bios_Vendor], [Bios_Version], [EC_Firmware_Major_Release], [EC_Firmware_Minor_Release], [System_Family], [System_Manufacturer], [System_Product_Name], [System_SKU], [System_Version], [Responsible], [Import_Source], [Import_Source_Details], [CPU_Speed], [Domain_Name], [IP_Address], [Network_Card], [Subnet_Mask], [MAC_Address], [Software_Publisher], [Accessible_To], [Warranty_Description], [Warranty_Start_Date], [Warranty_Status], [Warranty_ID], [Asset_Expires_], [Folder_Security], [Account_Type], [Account_Name], [VM_Platform], [VM_Type], [Host_Server], [VM_URL], [Source_Type], [Source_Name], [Import_Alias_1], [Import_Alias_2], [Import_Alias_3], [Last_Imported_Date], [Last_Imported_Time], [Asset_Request_Level_1_Approvers], [Asset_Request_Level_2_Approvers], [Asset_Request_Level_3_Approvers], [Asset_Request_Owner], [Asset_Request_Work_Team], [Minimum_Provisioning_Time], [Request_Priority], [Track_As], [Minimum_Quantity], [Current_Quantity], [Maximum_Quantity], [Need_to_Order_Quantity], [Allocation_Units], [Total_Allocated], [Total_Purchased], [Total_Costs_Over_Time], [Average_Total_Cost_Per_Unit], [Amount_to_Order], [Allocated_to_Department], [Allocated_to_Unit_Division], [Allocated_to_Project], [Can_Be_Requested], [Visible], [Software_Discovery_Mappings], [CPU_Count], [CPU_Cores_Count], [Primary_Owner], [Source_Timestamp], [Last_Imported_Timestamp], [Top_Console_User], [Visible_Request_Fields], [Request_Required_By_Date], [Request_Required_By_Time], [Number_Requested], [For_international_travel], [Requested_Until], [Request_Warning_Message], [Need_an_RSA_token], [Deliver_To], [Special_Instructions], [Laptop_Accessories_Needed], [For_Travel], [Litigation_Software], [Content_Loaded_on_Device], [Matter_Number], [Matter_Name], [Reason_for_Request], [Training_is_Requested], [Device_will_contain_Confidential_Data], [Needed_for_Device], [No_Approval_Needed_Level_1], [No_Approval_Needed_Level_2], [No_Approval_Needed_Level_3], [SIM_Card_Number], [IMEI], [Phone_Number], [Auto_Import_Installations], [Associated_Cost], [Approval_Needed_For], [Allocation_Rule], [Requested_Until_Time], [Related_License], [Location], [Sample_Data], [Environment], [Authorization_Required], [Application], [Access_Level_Is_Required_], [Training_Is_Required_], [ComputerName_Is_Required_], [Application_Is_Required_], [Vendor_Is_Required_], [Printer_Name_Is_Required_], [Active_Directory_Groups_Is_Required_], [Is_Missing_Required_Value], [Training_Level_Is_Required_], [Share_Name_Is_Required_], [Access_Code_Location_Is_Required_], [Room_Number], [Room_Number_Is_Required_], [Fileshare_Department_Is_Required_], [Invoice_Date], [Asset_Status], [PO_Number], [Purchased_From], [Contract_Duration__Years_], [Serial_Number_Is_Required_], [Auto_Create_License_Record], [Level_1], [Level_2], [Container], [Key], [Version], [Edition], [Allocated_to_Device___Computer], [dActualIRTDate], [dActualIRTTime], [ComputerChoiceList], [dTargetClosureTime], [dTargetIRT], [nAssociatedSLA], [Minimum_Approvers_Required], [Needs_Attention___Not_Seen_Recently], [Related_Bulk_Asset], [Asset_Life_Cycle_Stage], [Related_HelpDesk_Issues], [Key_Discovery___Application_Name], [Borrowable], [Software_Category], [Recurring_Costs], [Custom_Approvers], [Is_Bulk_Instance], [Reference_ID], [Family], [Source_Creation_Date], [View_Details], [Send_For_Repair], [Return_to_Storage_Location], [Check_In], [nEscalationLevel], [nServiceTypeID], [dEscalationTimestamp], [nEscalationOn], [nSeverityID], [nCumulativePausedTime], [LastAudit], [Check_In_Asset], [Check_Out_Asset], [Relevant_to_Data_Protection], [To_Be_Deleted], [Lost_Date], [Abbreviation] ) 
	VALUES (497, 3, N'-1', 25, CAST(0x0000A98E00000000 AS DateTime), CAST(0xFFFFFFFE011DDD40 AS DateTime), NULL, NULL, CAST(0x0000A98E00000000 AS DateTime), CAST(0x0000A98E00000000 AS DateTime), CAST(0xFFFFFFFE011DDD40 AS DateTime), CAST(0xFFFFFFFE011DDE6C AS DateTime), NULL, 2, NULL, 2, NULL, N'CBCO00003', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'CBCO00003', NULL, 0.0000, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, 0, NULL, 314, 100, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0.0000, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL)

GO

/*** COPY DATA FROM LOCATION TO ASSET_LOCATION ****/
UPDATE [tblDts]
	SET [Asset_Location]=[Location]
	WHERE [Asset_Location] IS NULL AND [Location] IS NOT NULL

GO

/*** Search and Correct, assets assigned to a container which no longer exists ***/
DECLARE @TableAssets Table (id INTEGER)
DECLARE @IdAsset INTEGER
DECLARE Cur CURSOR FOR
SELECT Distinct(Container) FROM [Resources01_Dat]..tblDts where Container is not NULL

OPEN Cur

FETCH NEXT FROM Cur Into @IdAsset

WHILE (@@FETCH_STATUS = 0)
BEGIN
	DECLARE @type INTEGER
	SELECT @type = Track_As  FROM [Resources01_Dat]..tblDts  Where nID = @IdAsset
	IF @type <> 3
	BEGIN
		Update [Resources01_Dat]..tblDts SET Container = NULL WHERE Container = @IdAsset
	END
	FETCH NEXT FROM Cur Into @IdAsset
END

CLOSE Cur
DEALLOCATE Cur

GO

