-- Move Win32_Environment attribute data to concrete table
PRINT N'--1. Creating View vw_Win32_Environment...'
GO
CREATE view [dbo].[vw_Win32_Environment]
as
 WITH SourceTable
AS (
	SELECT ELMT.DeviceGuid
		,ELMT.DateCreated
		,ELMT.ManagedElementGuid
		,COALESCE(ADATA.TextValue, CAST(CAST(ADATA.NumericValue AS BIGINT) AS NVARCHAR(50)), CAST(ADATA.DateTimeValue AS NVARCHAR(50)), CAST(ADATA.GuidValue AS NVARCHAR(50)), CAST(ADATA.BooleanValue AS VARCHAR(5)),  NULL) AS AttributeValue
		,ADATA.ManagedElementAttributeTypeGuid
		,ELMT.ManagedElementTypeGuid
	FROM dbo.ManagedElementAttributeData ADATA WITH (NOLOCK)
	INNER JOIN dbo.ManagedElement ELMT WITH (INDEX = 1) ON ADATA.ManagedElementGuid = ELMT.ManagedElementGuid
	WHERE ELMT.ManagedElementTypeGuid =  cast('7A455641-0D9B-4204-857D-585137D4F243' AS UNIQUEIDENTIFIER)  
	)
    select 
		 d.DeviceGuid
		,DateCreated
		,ManagedElementGuid
		,ManagedElementTypeGuid				
        ,[Caption] = cast([A50C4497-86A8-4839-A7C8-B402BEFE41EF] as nvarchar(500))
        ,[Description] = cast([3819003C-EE4C-4947-BA82-3490E3B59FF0] as nvarchar(2000))
        ,[InstallDate] = cast([80EB5634-D317-460F-903E-1A702939FB9B] as datetime)
        ,[Name] = cast([560DB496-4170-404B-A9F8-B84E8765C925] as nvarchar(1000))
        ,[Status] = cast([D43F67DF-EA19-4FC8-9C78-88C52A089DCC] as nvarchar(1000))
        ,[SystemVariable] = cast([99441820-5B3D-4F0C-9025-C52A819800BE] as bit)
        ,[UserName] = cast([CE6BE999-04FA-4D26-AF96-31020DE6F72E] as nvarchar(1000))
        ,[VariableValue] = cast([7182B744-C32C-4E89-B459-463093E81E5B] as nvarchar(3000))
    from  
          SourceTable
          pivot
            (
	           max(AttributeValue) FOR ManagedElementAttributeTypeGuid in (
[A50C4497-86A8-4839-A7C8-B402BEFE41EF],[3819003C-EE4C-4947-BA82-3490E3B59FF0],[80EB5634-D317-460F-903E-1A702939FB9B],[560DB496-4170-404B-A9F8-B84E8765C925],[D43F67DF-EA19-4FC8-9C78-88C52A089DCC],[99441820-5B3D-4F0C-9025-C52A819800BE],[CE6BE999-04FA-4D26-AF96-31020DE6F72E],[7182B744-C32C-4E89-B459-463093E81E5B]
             )) T
	JOIN Device d ON T.DeviceGuid = d.DeviceGuid 

GO

PRINT N' -- 2. Inserting data to Asset_Win32_Environment table from ManagedElementAttributeData table...'
INSERT INTO [dbo].[Asset_Win32_Environment]
           ([Win32_EnvironmentGuid]
           ,[DeviceGuid]
		   ,[Caption]	
		   ,[Description]	
		   ,[InstallDate]
		   ,[Status]
		   ,[Name]
		   ,[SystemVariable]
		   ,[UserName]
		   ,[VariableValue]            
           ,[DateCreated]
           ,[DateModified])
SELECT 
     [ManagedElementGuid]
	,[DeviceGuid]
	,[Caption]	
	,[Description]	
	,[InstallDate]
	,[Status]
	,[Name]
	,[SystemVariable]
	,[UserName]
	,[VariableValue]            
    ,[DateCreated]     	
	,DateModified = GETDATE()
  FROM [dbo].[vw_Win32_Environment]
GO

DROP VIEW [vw_Win32_Environment]
GO


-- Move SMBios_Processor attribute dat to concrete table
PRINT N'--3. Creating View vw_SMBios_Processor...'
GO
CREATE view [dbo].[vw_SMBios_Processor]
as
 WITH SourceTable
AS (
	SELECT ELMT.DeviceGuid
		,ELMT.DateCreated
		,ELMT.ManagedElementGuid
		,COALESCE(ADATA.TextValue, CAST(CAST(ADATA.NumericValue AS BIGINT) AS NVARCHAR(50)), CAST(ADATA.DateTimeValue AS NVARCHAR(50)), CAST(ADATA.GuidValue AS NVARCHAR(50)), CAST(ADATA.BooleanValue AS VARCHAR(5)),  NULL) AS AttributeValue
		,ADATA.ManagedElementAttributeTypeGuid
		,ELMT.ManagedElementTypeGuid
	FROM dbo.ManagedElementAttributeData ADATA WITH (NOLOCK)
	INNER JOIN dbo.ManagedElement ELMT WITH (INDEX = 1) ON ADATA.ManagedElementGuid = ELMT.ManagedElementGuid
	WHERE ELMT.ManagedElementTypeGuid =  cast('9B45AB98-BA85-43EF-8E79-82B3FCDA7755' AS UNIQUEIDENTIFIER)  
	)
    select 
		 d.DeviceGuid
		,DateCreated
		,ManagedElementGuid
		,ManagedElementTypeGuid				
        
		
        ,[AssetTag] = cast([2FF15C82-E89C-4E7B-8694-900C35F7DD1D] as nvarchar(100))
        ,[CoreCount] = cast([85D0252D-1E02-4A91-9355-3BF773A7343F] as tinyint)
        ,[CoreEnabled] = cast([F6ED5E76-D271-4452-B5F0-D7995A18EE79] as tinyint)
		,[CpuNumber] = cast([2B8C679E-8818-49F7-BEBD-FC2E6E937377] as int)
        ,[CurrentSpeed] = cast([7A79B34A-3475-433A-8D0A-C63A7A56CD76] as int)
        ,[ExternalClock] = cast([BA56F181-5ACF-4618-BA4C-5B48452DFD37] as int)
        ,[Manufacturer] = cast([4114CCBC-A6D2-4FAF-93BA-4FE464E848C3] as nvarchar(100))
        ,[MaxSpeed] = cast([98CA2ACD-739C-4602-8C20-EE5A81BB0EBF] as int)
        ,[ProcCharacteristics] = cast([B24C2EC1-4419-4D5E-B214-4351C1E476F4] as int)
        ,[ProcessorFamily] = cast([6538F6FC-00EC-4B74-9781-0F2704730E03] as tinyint)
        ,[ProcessorID] = cast([0F04CA55-1227-4523-9844-4E9FA9AD04AB] as bigint)
        ,[ProcessorType] = cast([D8109ACF-93E1-45FD-95B6-CA6DC675EE63] as tinyint)
        ,[ProcUpgrade] = cast([F415826F-5065-43CD-B5C4-37E18186958C] as tinyint)
        ,[SerialNumber] = cast([07267B18-FF0D-4EE7-8122-CD365D586379] as nvarchar(100))
        ,[SocketDesignation] = cast([DC940E01-C5E1-4A36-A021-E63D9D521BE8] as nvarchar(100))
        ,[Status] = cast([C4CC8E49-61FF-4082-AB90-2E8E0EB5FB7A] as tinyint)
        ,[ThreadCount] = cast([D1332299-35C1-44F4-A1B9-D9339A5A2D51] as tinyint)
        ,[Version] = cast([AC78417D-9999-4824-9CC4-A55D3B372C95] as nvarchar(100))
        ,[Voltage] = cast([44CFFF3D-2DF4-4AF5-977D-E348C3146450] as tinyint)
    from  
          SourceTable
            pivot
            (
	           max(AttributeValue) FOR ManagedElementAttributeTypeGuid in (
[2FF15C82-E89C-4E7B-8694-900C35F7DD1D],[85D0252D-1E02-4A91-9355-3BF773A7343F],[F6ED5E76-D271-4452-B5F0-D7995A18EE79],[2B8C679E-8818-49F7-BEBD-FC2E6E937377],[7A79B34A-3475-433A-8D0A-C63A7A56CD76],[BA56F181-5ACF-4618-BA4C-5B48452DFD37],[4114CCBC-A6D2-4FAF-93BA-4FE464E848C3],[98CA2ACD-739C-4602-8C20-EE5A81BB0EBF],[B24C2EC1-4419-4D5E-B214-4351C1E476F4],[6538F6FC-00EC-4B74-9781-0F2704730E03],[0F04CA55-1227-4523-9844-4E9FA9AD04AB],[D8109ACF-93E1-45FD-95B6-CA6DC675EE63],[F415826F-5065-43CD-B5C4-37E18186958C],[07267B18-FF0D-4EE7-8122-CD365D586379],[DC940E01-C5E1-4A36-A021-E63D9D521BE8],[C4CC8E49-61FF-4082-AB90-2E8E0EB5FB7A],[D1332299-35C1-44F4-A1B9-D9339A5A2D51],[AC78417D-9999-4824-9CC4-A55D3B372C95],[44CFFF3D-2DF4-4AF5-977D-E348C3146450]
            )) T
	JOIN Device d ON T.DeviceGuid = d.DeviceGuid 
GO

PRINT N' -- 4. Inserting data to Asset_SMBios_Processor table from ManagedElementAttributeData table...'
INSERT INTO [dbo].[Asset_SMBios_Processor]
           ([SMBios_ProcessorGuid]
           ,[DeviceGuid]
		   	
			,[AssetTag] 
			,[CoreCount] 
			,[CoreEnabled] 
			,[CpuNumber]
			,[CurrentSpeed] 
			,[ExternalClock] 
			,[Manufacturer] 
			,[MaxSpeed] 
			,[ProcCharacteristics] 
			,[ProcessorFamily]
			,[ProcessorID] 
			,[ProcessorType] 
			,[ProcUpgrade] 
			,[SerialNumber]
			,[SocketDesignation] 
			,[Status] 
			,[ThreadCount] 
			,[Version]
			,[Voltage]


		   ,[DateCreated]
           ,[DateModified])
SELECT 
     [ManagedElementGuid]
	,[DeviceGuid]
	  	
	,[AssetTag] 
	,[CoreCount] 
	,[CoreEnabled] 
	,[CpuNumber]
	,[CurrentSpeed] 
	,[ExternalClock] 
	,[Manufacturer] 
	,[MaxSpeed] 
	,[ProcCharacteristics] 
	,[ProcessorFamily]
	,[ProcessorID] 
	,[ProcessorType] 
	,[ProcUpgrade] 
	,[SerialNumber]
	,[SocketDesignation] 
	,[Status] 
	,[ThreadCount] 
	,[Version]
	,[Voltage]
	     
    ,[DateCreated]     	
	,DateModified = GETDATE()
  FROM [dbo].[vw_SMBios_Processor]
GO

DROP VIEW [vw_SMBios_Processor]
GO

-- Move SMBios_MemoryDevice attribute data to concrete table
PRINT N'--5. Creating View vw_SMBios_MemoryDevice...'
GO
CREATE view [dbo].[vw_SMBios_MemoryDevice]
as
 WITH SourceTable
AS (
	SELECT ELMT.DeviceGuid
		,ELMT.DateCreated
		,ELMT.ManagedElementGuid
		,COALESCE(ADATA.TextValue, CAST(CAST(ADATA.NumericValue AS BIGINT) AS NVARCHAR(50)), CAST(ADATA.DateTimeValue AS NVARCHAR(50)), CAST(ADATA.GuidValue AS NVARCHAR(50)), CAST(ADATA.BooleanValue AS VARCHAR(5)),  NULL) AS AttributeValue
		,ADATA.ManagedElementAttributeTypeGuid
		,ELMT.ManagedElementTypeGuid
	FROM dbo.ManagedElementAttributeData ADATA WITH (NOLOCK)
	INNER JOIN dbo.ManagedElement ELMT WITH (INDEX = 1) ON ADATA.ManagedElementGuid = ELMT.ManagedElementGuid
	WHERE ELMT.ManagedElementTypeGuid =  cast('B954046B-8F41-454C-BB04-3552A8DA5BF6' AS UNIQUEIDENTIFIER)  
	)
    select 
		 d.DeviceGuid
		,DateCreated
		,ManagedElementGuid
		,ManagedElementTypeGuid				
		
        ,[AssetTag] = cast([00E93582-D6CF-417B-9B3D-166FBB604718] as nvarchar(100))
        ,[Attributes] = cast([14CB59C5-3A62-462B-B8A9-B45068351E3A] as tinyint)
        ,[BankLocator] = cast([0B178D39-848E-400C-B41F-C013A9FF7064] as nvarchar(100))
        ,[DataWidth] = cast([51945306-12E7-464E-94EF-B75B120F75B3] as int)
        ,[DeviceLocator] = cast([F8D1CB38-A282-43C7-AE49-3E30198127E5] as nvarchar(100))
        ,[DevSet] = cast([4C6ED93D-22AB-4C50-AAEB-C3ECF9727A88] as tinyint)
        ,[Manufacturer] = cast([E2C0254D-27AB-486B-A313-F18213C04B86] as nvarchar(100))
        ,[MemType] = cast([71B64E8B-81C5-4B3C-AD6D-D06CBE054363] as tinyint)
        ,[PartNumber] = cast([B0D7DABF-7622-47CD-8E29-26AE688A879F] as nvarchar(100))
        ,[SerialNumber] = cast([9260B883-5D22-4E67-874C-BB4733FB8CB5] as nvarchar(100))
        ,[Size] = cast([A4D13378-FCCA-40E1-A83D-E9A97BB346DB] as int)
        ,[Speed] = cast([2199E622-06C9-4B78-B5FA-65E347EB9E9F] as int)
        ,[TotalWidth] = cast([BC32C209-93A3-4105-8A90-4264B8262F6D] as int)
        ,[TypeDetails] = cast([54BB2AEF-3070-44AB-AB0A-9A75A91E108E] as int)
      
    from  
          SourceTable
          pivot
            (
	           max(AttributeValue) FOR ManagedElementAttributeTypeGuid in (
[00E93582-D6CF-417B-9B3D-166FBB604718],[14CB59C5-3A62-462B-B8A9-B45068351E3A],[0B178D39-848E-400C-B41F-C013A9FF7064],[51945306-12E7-464E-94EF-B75B120F75B3],[F8D1CB38-A282-43C7-AE49-3E30198127E5],[4C6ED93D-22AB-4C50-AAEB-C3ECF9727A88],[E2C0254D-27AB-486B-A313-F18213C04B86],[71B64E8B-81C5-4B3C-AD6D-D06CBE054363],[B0D7DABF-7622-47CD-8E29-26AE688A879F],[9260B883-5D22-4E67-874C-BB4733FB8CB5],[A4D13378-FCCA-40E1-A83D-E9A97BB346DB],[2199E622-06C9-4B78-B5FA-65E347EB9E9F],[BC32C209-93A3-4105-8A90-4264B8262F6D],[54BB2AEF-3070-44AB-AB0A-9A75A91E108E]
             )) T
	JOIN Device d ON T.DeviceGuid = d.DeviceGuid 
GO

PRINT N' -- 6. Inserting data to Asset_SMBios_MemoryDevice table from ManagedElementAttributeData table...'
INSERT INTO [dbo].[Asset_SMBios_MemoryDevice]
(
	 [SMBios_MemoryDeviceGuid]
    ,[DeviceGuid]
	,[AssetTag] 
	,[Attributes] 
	,[BankLocator] 
	,[DataWidth]
	,[DeviceLocator] 
	,[DevSet] 
	,[Manufacturer] 
	,[MemType]
	,[PartNumber] 
	,[SerialNumber] 
	,[Size] 
	,[Speed] 
	,[TotalWidth] 
	,[TypeDetails] 
		     
    ,[DateCreated]
    ,[DateModified]
)
SELECT 
     [ManagedElementGuid]
	,[DeviceGuid]

	,[AssetTag] 
	,[Attributes] 
	,[BankLocator] 
	,[DataWidth]
	,[DeviceLocator] 
	,[DevSet] 
	,[Manufacturer] 
	,[MemType]
	,[PartNumber] 
	,[SerialNumber] 
	,[Size] 
	,[Speed] 
	,[TotalWidth] 
	,[TypeDetails] 
	   
    ,[DateCreated]     	
	,DateModified = GETDATE()
  FROM [dbo].[vw_SMBios_MemoryDevice]
GO

DROP VIEW [vw_SMBios_MemoryDevice]
GO 


-- Move Win32_Printer attribute data to concrete table
PRINT N'--7. Creating View vw_Win32_Printer...'
GO
CREATE view [dbo].[vw_Win32_Printer]
as
 WITH SourceTable
AS (
	SELECT ELMT.DeviceGuid
		,ELMT.DateCreated
		,ELMT.ManagedElementGuid
		,COALESCE(ADATA.TextValue, CAST(CAST(ADATA.NumericValue AS BIGINT) AS NVARCHAR(50)), CAST(ADATA.DateTimeValue AS NVARCHAR(50)), CAST(ADATA.GuidValue AS NVARCHAR(50)), CAST(ADATA.BooleanValue AS VARCHAR(5)),  NULL) AS AttributeValue
		,ADATA.ManagedElementAttributeTypeGuid
		,ELMT.ManagedElementTypeGuid
	FROM dbo.ManagedElementAttributeData ADATA WITH (NOLOCK)
	INNER JOIN dbo.ManagedElement ELMT WITH (INDEX = 1) ON ADATA.ManagedElementGuid = ELMT.ManagedElementGuid
	WHERE ELMT.ManagedElementTypeGuid =  cast('A5B54431-A5B8-4D35-9F11-6C7337EE47A6' AS UNIQUEIDENTIFIER)  
	)
    select 
		 d.DeviceGuid
		,DateCreated
		,ManagedElementGuid
		,ManagedElementTypeGuid			
		
        ,[Attributes] = cast([408B486A-4E0A-4ACA-8B3C-C318C249C5E7] as bigint)
        ,[Availability] = cast([07E3AA68-B2AB-4CD1-A6CB-C7B333390692] as int)
        ,[AvailableJobSheets] = cast([3F38DD04-B70E-4550-B3CC-D9F82D643070] as nvarchar(3000))
        ,[AveragePagesPerMinute] = cast([39093AA1-6D6C-45B8-B21C-BFCD18685EE2] as bigint)
        ,[Capabilities] = cast([F5F814A3-4BBF-4591-BA3A-553A0FA02114] as nvarchar(3000))
        ,[CapabilityDescriptions] = cast([891F0B01-314B-449D-818E-337D2EF325BF] as nvarchar(3000))
        ,[Caption] = cast([294E73D9-80C5-469D-A790-61888F7E3AE7] as nvarchar(500))
        ,[CharSetsSupported] = cast([362CD5BB-868E-4029-9252-26D602DE62FF] as nvarchar(3000))
        ,[Comment] = cast([C378EE42-4A16-4DF9-A53F-D8C969F1BFC9] as nvarchar(1000))
        ,[ConfigManagerErrorCode] = cast([E75DDBF6-DE72-40D1-8F27-4D3D3775FD1F] as bigint)
        ,[ConfigManagerUserConfig] = cast([8F880183-4727-4BF8-884C-F68423D48BB6] as bit)
        ,[CreationClassName] = cast([D727D02A-647E-46C8-8B30-B32B7E6C9C23] as nvarchar(1000))
        ,[CurrentCapabilities] = cast([924B4F40-F496-4C1E-80C9-F9E8C7E689AE] as nvarchar(1000))
        ,[CurrentCharSet] = cast([93AAC7CD-DEB2-4071-B6FD-D4A8D3CBFBF3] as nvarchar(1000))
        ,[CurrentLanguage] = cast([AE0BA2F6-79DD-4BF1-8001-A590E4F3E4CD] as int)
        ,[CurrentMimeType] = cast([FE16E75A-A906-4F42-AB03-D4EF99AFDC08] as nvarchar(1000))
        ,[CurrentNaturalLanguage] = cast([EA45F46F-DF60-4CC3-B197-25602F4F6354] as nvarchar(1000))
        ,[CurrentPaperType] = cast([3282FB16-E509-4228-9AA7-6E39A4ACFFF0] as nvarchar(1000))
        ,[Default] = cast([AB98B32F-92A8-4E95-B07E-8B820D2E6919] as bit)
        ,[DefaultCapabilities] = cast([7A2176C7-291B-45BB-90D8-DA1BE2180DA0] as nvarchar(3000))
        ,[DefaultCopies] = cast([1CB218BD-4540-4E19-887A-763471857EA4] as bigint)
        ,[DefaultLanguage] = cast([45BA107C-A3CB-4804-B5B3-CBFB68521552] as int)
        ,[DefaultMimeType] = cast([01B84F8D-61F6-4CF4-91EF-CC49760350CC] as nvarchar(1000))
        ,[DefaultNumberUp] = cast([66EB20B4-1636-470B-9E58-F53ABA446FFE] as bigint)
        ,[DefaultPaperType] = cast([5EBB787A-CA71-434F-AF7A-509DA5E9E374] as nvarchar(1000))
        ,[DefaultPriority] = cast([D62123F3-3478-47D0-8BA8-99F532141353] as bigint)
        ,[Description] = cast([EC1ED178-98C3-4F69-AC29-5637EF8E5829] as nvarchar(2000))
        ,[DetectedErrorState] = cast([B473EDB2-C512-4BBC-B44E-F7BA558086D9] as int)
        ,[DeviceID] = cast([CB67818E-4D3A-4709-8589-1D884CC202ED] as nvarchar(1000))
        ,[Direct] = cast([A49B998A-031C-439A-AEC6-D9211FA79471] as bit)
        ,[DoCompleteFirst] = cast([8D7D1C59-B4AA-4360-96DF-2F423330D73F] as bit)
        ,[DriverName] = cast([8865CD71-742D-441A-B91B-970907FE9B90] as nvarchar(1000))
        ,[EnableBIDI] = cast([AFF39C09-2BD6-455A-8A1E-3CC83B042FA8] as bit)
        ,[EnableDevQueryPrint] = cast([BF957BCE-2175-4EDC-BEA0-9C8199DF255A] as bit)
        ,[ErrorCleared] = cast([934797B6-FA1E-4F51-96DB-121A14328F66] as bit)
        ,[ErrorDescription] = cast([0B5DF6B0-20DF-4FBA-B6DB-13C9AB842FE4] as nvarchar(2000))
        ,[ErrorInformation] = cast([40AA79A8-0AE1-4E0A-8EAF-CE328E86A1E1] as nvarchar(3000))
        ,[ExtendedDetectedErrorState] = cast([189B21A6-6E6E-479F-936A-B8CBB553DFBF] as int)
        ,[ExtendedPrinterStatus] = cast([2C1BDEEF-AB9D-48A2-B299-C7DCE50054AF] as int)
        ,[Hidden] = cast([EAD864C4-0047-4BBD-AC76-52A219C3BA47] as bit)
        ,[HorizontalResolution] = cast([DBDDE0E4-959E-4D10-A047-F027D2E97C13] as bigint)
        ,[InstallDate] = cast([8661CF93-39C6-4C79-9B94-3457332C4925] as datetime)
        ,[JobCountSinceLastReset] = cast([3ECF9330-FF26-4F89-B23A-FF005525E7A8] as bigint)
        ,[KeepPrintedJobs] = cast([794B1DF9-0376-47CD-A4E4-B73CD5621410] as bit)
        ,[LanguagesSupported] = cast([D889B0D9-2A00-49E4-8220-8138B6B9C1C1] as nvarchar(3000))
        ,[LastErrorCode] = cast([87760D28-B835-4261-ABD2-8AC5019265E3] as bigint)
        ,[Local] = cast([B333C053-5BA0-4D87-BC5A-FA14BC632A64] as bit)
        ,[Location] = cast([0A072926-27FB-474A-BE54-394589143E95] as nvarchar(1000))
        ,[MarkingTechnology] = cast([4DBB2195-DF9A-4A20-936B-3672D6F5FB33] as int)
        ,[MaxCopies] = cast([175BA3E0-1A61-460A-ACAB-F9F5EDAD2745] as bigint)
        ,[MaxNumberUp] = cast([5274ACDB-389C-4BF7-B16F-D456D46ADFAC] as bigint)
        ,[MaxSizeSupported] = cast([C878E5B5-C06A-4030-AD5A-DACCFF47605B] as bigint)
        ,[MimeTypesSupported] = cast([8DD9A6B2-EC50-4630-B135-B6F41993965E] as nvarchar(3000))
        ,[Name] = cast([BD1967E2-25C3-4B4C-97E1-C186C0A939B0] as nvarchar(1000))
        ,[NaturalLanguagesSupported] = cast([BCFA9CDD-07B4-4625-9493-703733B6C619] as nvarchar(3000))
        ,[Network] = cast([C172412B-C09C-4748-93E3-EF7C98FDB8E8] as bit)
        ,[PaperSizesSupported] = cast([D0FE3A54-727C-49F7-83DE-B39CBEFCA4AA] as nvarchar(3000))
        ,[PaperTypesAvailable] = cast([FD66CF8B-9339-4D7C-9131-149E55C5862F] as nvarchar(3000))
        ,[Parameters] = cast([54062A3E-2E41-425B-93F7-B7AB2252A8FF] as nvarchar(1000))
        ,[PNPDeviceID] = cast([4B963D48-723D-4C37-A0B2-95FF432658B5] as nvarchar(1000))
        ,[PortName] = cast([F1B3AC4F-BA3B-4203-8BDF-8F6B28BF1187] as nvarchar(1000))
        ,[PowerManagementCapabilities] = cast([05E70BD5-2D46-43D1-8673-4665EF797923] as nvarchar(3000))
        ,[PowerManagementSupported] = cast([E0E02531-404F-4ACB-ABC9-A0FFC9304C63] as bit)
        ,[PrinterPaperNames] = cast([8E00E148-8488-41DB-911B-71C95E646810] as nvarchar(1000))
        ,[PrinterState] = cast([33B91C72-4634-45DF-B565-55C0C4F55382] as bigint)
        ,[PrinterStatus] = cast([51523B08-75BF-4FF6-82A9-E2BFED71A3B3] as int)
        ,[PrintJobDataType] = cast([5B7EEFCD-D246-4F5C-A88B-ADAF368422D6] as nvarchar(1000))
        ,[PrintProcessor] = cast([FB1B0689-5F1E-4D7C-947C-0839C591CABE] as nvarchar(1000))
        ,[Priority] = cast([2C79F32E-97E2-43E7-9C87-0509007F8DB2] as bigint)
        ,[Published] = cast([B1B1AEE0-326E-4908-86AB-64148A8760C3] as bit)
        ,[Queued] = cast([A823C2C6-2875-44DA-8946-78353D904EF1] as bit)
        ,[RawOnly] = cast([40450B00-F04C-4EB9-8134-7C96C0235CB6] as bit)
        ,[SeparatorFile] = cast([20F6A16B-01B4-4A35-B15B-5A088428EB20] as nvarchar(1000))
        ,[ServerName] = cast([052A623B-0ABA-4536-9729-5748419C2ED3] as nvarchar(1000))
        ,[Shared] = cast([9FFDD23B-ABB2-4800-955D-9FEA044C5AD2] as bit)
        ,[ShareName] = cast([293D5608-2B26-4B02-B6B5-92FCEEADEC91] as nvarchar(1000))
        ,[SpoolEnabled] = cast([D0EF6F56-3F95-400E-8A36-45211B69E7D0] as bit)
        ,[StartTime] = cast([F16E2400-1670-4CC8-AFF7-709B9039C7E7] as datetime)
        ,[Status] = cast([D80A724D-2998-4DB2-8756-1F471B5CAD67] as nvarchar(1000))
        ,[StatusInfo] = cast([8486275F-9A80-4B57-9F7F-6F5F66760FDF] as int)
        ,[SystemCreationClassName] = cast([19B2CB45-EC5A-4C41-8E52-A3B8CA120B16] as nvarchar(1000))
        ,[SystemName] = cast([2ADBCD61-F1AD-4559-AA36-08DD3CDCC575] as nvarchar(1000))
        ,[TimeOfLastReset] = cast([A483AE30-EFF4-4BB4-8C50-06B406074C60] as datetime)
        ,[UntilTime] = cast([A88D2F57-02F0-4B9F-8AF8-ABC471FD8F69] as datetime)
        ,[VerticalResolution] = cast([35635122-0A0D-4298-86CA-AE7F7A20D930] as bigint)
        ,[WorkOffline] = cast([15DF19D2-2086-4358-A183-45D426BFEA6D] as bit)

    from  
          SourceTable
           pivot
            (
	           max(AttributeValue) FOR ManagedElementAttributeTypeGuid in (
[408B486A-4E0A-4ACA-8B3C-C318C249C5E7],[07E3AA68-B2AB-4CD1-A6CB-C7B333390692],[3F38DD04-B70E-4550-B3CC-D9F82D643070],[39093AA1-6D6C-45B8-B21C-BFCD18685EE2],[F5F814A3-4BBF-4591-BA3A-553A0FA02114],[891F0B01-314B-449D-818E-337D2EF325BF],[294E73D9-80C5-469D-A790-61888F7E3AE7],[362CD5BB-868E-4029-9252-26D602DE62FF],[C378EE42-4A16-4DF9-A53F-D8C969F1BFC9],[E75DDBF6-DE72-40D1-8F27-4D3D3775FD1F],[8F880183-4727-4BF8-884C-F68423D48BB6],[D727D02A-647E-46C8-8B30-B32B7E6C9C23],[924B4F40-F496-4C1E-80C9-F9E8C7E689AE],[93AAC7CD-DEB2-4071-B6FD-D4A8D3CBFBF3],[AE0BA2F6-79DD-4BF1-8001-A590E4F3E4CD],[FE16E75A-A906-4F42-AB03-D4EF99AFDC08],[EA45F46F-DF60-4CC3-B197-25602F4F6354],[3282FB16-E509-4228-9AA7-6E39A4ACFFF0],[AB98B32F-92A8-4E95-B07E-8B820D2E6919],[7A2176C7-291B-45BB-90D8-DA1BE2180DA0],[1CB218BD-4540-4E19-887A-763471857EA4],[45BA107C-A3CB-4804-B5B3-CBFB68521552],[01B84F8D-61F6-4CF4-91EF-CC49760350CC],[66EB20B4-1636-470B-9E58-F53ABA446FFE],[5EBB787A-CA71-434F-AF7A-509DA5E9E374],[D62123F3-3478-47D0-8BA8-99F532141353],[EC1ED178-98C3-4F69-AC29-5637EF8E5829],[B473EDB2-C512-4BBC-B44E-F7BA558086D9],[CB67818E-4D3A-4709-8589-1D884CC202ED],[A49B998A-031C-439A-AEC6-D9211FA79471],[8D7D1C59-B4AA-4360-96DF-2F423330D73F],[8865CD71-742D-441A-B91B-970907FE9B90],[AFF39C09-2BD6-455A-8A1E-3CC83B042FA8],[BF957BCE-2175-4EDC-BEA0-9C8199DF255A],[934797B6-FA1E-4F51-96DB-121A14328F66],[0B5DF6B0-20DF-4FBA-B6DB-13C9AB842FE4],[40AA79A8-0AE1-4E0A-8EAF-CE328E86A1E1],[189B21A6-6E6E-479F-936A-B8CBB553DFBF],[2C1BDEEF-AB9D-48A2-B299-C7DCE50054AF],[EAD864C4-0047-4BBD-AC76-52A219C3BA47],[DBDDE0E4-959E-4D10-A047-F027D2E97C13],[8661CF93-39C6-4C79-9B94-3457332C4925],[3ECF9330-FF26-4F89-B23A-FF005525E7A8],[794B1DF9-0376-47CD-A4E4-B73CD5621410],[D889B0D9-2A00-49E4-8220-8138B6B9C1C1],[87760D28-B835-4261-ABD2-8AC5019265E3],[B333C053-5BA0-4D87-BC5A-FA14BC632A64],[0A072926-27FB-474A-BE54-394589143E95],[4DBB2195-DF9A-4A20-936B-3672D6F5FB33],[175BA3E0-1A61-460A-ACAB-F9F5EDAD2745],[5274ACDB-389C-4BF7-B16F-D456D46ADFAC],[C878E5B5-C06A-4030-AD5A-DACCFF47605B],[8DD9A6B2-EC50-4630-B135-B6F41993965E],[BD1967E2-25C3-4B4C-97E1-C186C0A939B0],[BCFA9CDD-07B4-4625-9493-703733B6C619],[C172412B-C09C-4748-93E3-EF7C98FDB8E8],[D0FE3A54-727C-49F7-83DE-B39CBEFCA4AA],[FD66CF8B-9339-4D7C-9131-149E55C5862F],[54062A3E-2E41-425B-93F7-B7AB2252A8FF],[4B963D48-723D-4C37-A0B2-95FF432658B5],[F1B3AC4F-BA3B-4203-8BDF-8F6B28BF1187],[05E70BD5-2D46-43D1-8673-4665EF797923],[E0E02531-404F-4ACB-ABC9-A0FFC9304C63],[8E00E148-8488-41DB-911B-71C95E646810],[33B91C72-4634-45DF-B565-55C0C4F55382],[51523B08-75BF-4FF6-82A9-E2BFED71A3B3],[5B7EEFCD-D246-4F5C-A88B-ADAF368422D6],[FB1B0689-5F1E-4D7C-947C-0839C591CABE],[2C79F32E-97E2-43E7-9C87-0509007F8DB2],[B1B1AEE0-326E-4908-86AB-64148A8760C3],[A823C2C6-2875-44DA-8946-78353D904EF1],[40450B00-F04C-4EB9-8134-7C96C0235CB6],[20F6A16B-01B4-4A35-B15B-5A088428EB20],[052A623B-0ABA-4536-9729-5748419C2ED3],[9FFDD23B-ABB2-4800-955D-9FEA044C5AD2],[293D5608-2B26-4B02-B6B5-92FCEEADEC91],[D0EF6F56-3F95-400E-8A36-45211B69E7D0],[F16E2400-1670-4CC8-AFF7-709B9039C7E7],[D80A724D-2998-4DB2-8756-1F471B5CAD67],[8486275F-9A80-4B57-9F7F-6F5F66760FDF],[19B2CB45-EC5A-4C41-8E52-A3B8CA120B16],[2ADBCD61-F1AD-4559-AA36-08DD3CDCC575],[A483AE30-EFF4-4BB4-8C50-06B406074C60],[A88D2F57-02F0-4B9F-8AF8-ABC471FD8F69],[35635122-0A0D-4298-86CA-AE7F7A20D930],[15DF19D2-2086-4358-A183-45D426BFEA6D]
   )) T
	JOIN Device d ON T.DeviceGuid = d.DeviceGuid 

GO

PRINT N' -- 8. Inserting data to Asset_Win32_Printer table from ManagedElementAttributeData table...'
INSERT INTO[dbo].[Asset_Win32_Printer]
(
		[Win32_PrinterGuid]
		,[DeviceGuid]

		,[Attributes]				
		,[Availability]				
		,[AvailableJobSheets]		
		,[AveragePagesPerMinute]		
		,[Capabilities]			
		,[CapabilityDescriptions]	
		,[Caption]			
		,[CharSetsSupported]		
		,[Comment]			
		,[ConfigManagerErrorCode]	
		,[ConfigManagerUserConfig]			
		,[CreationClassName]		
		,[CurrentCapabilities]		
		,[CurrentCharSet]		
		,[CurrentLanguage]		
		,[CurrentMimeType]			
		,[CurrentNaturalLanguage]	
		,[CurrentPaperType]		
		,[Default]			
		,[DefaultCapabilities]		
		,[DefaultCopies]			
		,[DefaultLanguage]		
		,[DefaultMimeType]		
		,[DefaultNumberUp]		
		,[DefaultPaperType]		
		,[DefaultPriority]		
		,[Description]			
		,[DetectedErrorState]		
		,[DeviceID]			
		,[Direct]			
		,[DoCompleteFirst]		
		,[DriverName]			
		,[EnableBIDI]			
		,[EnableDevQueryPrint]		
		,[ErrorCleared]			
		,[ErrorDescription]		
		,[ErrorInformation]		
		,[ExtendedDetectedErrorState]	
		,[ExtendedPrinterStatus]		
		,[Hidden]			
		,[HorizontalResolution]		
		,[InstallDate]				
		,[JobCountSinceLastReset]	
		,[KeepPrintedJobs]		
		,[LanguagesSupported]		
		,[LastErrorCode]			
		,[Local]				
		,[Location]			
		,[MarkingTechnology]		
		,[MaxCopies]			
		,[MaxNumberUp]			
		,[MaxSizeSupported]		
		,[MimeTypesSupported]		
		,[Name]				
		,[NaturalLanguagesSupported]	
		,[Network]			
		,[PaperSizesSupported]		
		,[PaperTypesAvailable]		
		,[Parameters]			
		,[PNPDeviceID]			
		,[PortName]			
		,[PowerManagementCapabilities]	
		,[PowerManagementSupported]	
		,[PrinterPaperNames]		
		,[PrinterState]			
		,[PrinterStatus]			
		,[PrintJobDataType]		
		,[PrintProcessor]		
		,[Priority]			
		,[Published]			
		,[Queued]			
		,[RawOnly]			
		,[SeparatorFile]			
		,[ServerName]			
		,[Shared]			
		,[ShareName]			
		,[SpoolEnabled]			
		,[StartTime]			
		,[Status]			
		,[StatusInfo]			
		,[SystemCreationClassName]	
		,[SystemName]			
		,[TimeOfLastReset]		
		,[UntilTime]			
		,[VerticalResolution]		
		,[WorkOffline]			
		             
		,[DateCreated]
		,[DateModified])
SELECT 
     [ManagedElementGuid]
	,[DeviceGuid]
	,[Attributes]				
	,[Availability]				
	,[AvailableJobSheets]		
	,[AveragePagesPerMinute]		
	,[Capabilities]			
	,[CapabilityDescriptions]	
	,[Caption]			
	,[CharSetsSupported]		
	,[Comment]			
	,[ConfigManagerErrorCode]	
	,[ConfigManagerUserConfig]			
	,[CreationClassName]		
	,[CurrentCapabilities]		
	,[CurrentCharSet]		
	,[CurrentLanguage]		
	,[CurrentMimeType]			
	,[CurrentNaturalLanguage]	
	,[CurrentPaperType]		
	,[Default]			
	,[DefaultCapabilities]		
	,[DefaultCopies]			
	,[DefaultLanguage]		
	,[DefaultMimeType]		
	,[DefaultNumberUp]		
	,[DefaultPaperType]		
	,[DefaultPriority]		
	,[Description]			
	,[DetectedErrorState]		
	,[DeviceID]			
	,[Direct]			
	,[DoCompleteFirst]		
	,[DriverName]			
	,[EnableBIDI]			
	,[EnableDevQueryPrint]		
	,[ErrorCleared]			
	,[ErrorDescription]		
	,[ErrorInformation]		
	,[ExtendedDetectedErrorState]	
	,[ExtendedPrinterStatus]		
	,[Hidden]			
	,[HorizontalResolution]		
	,[InstallDate]				
	,[JobCountSinceLastReset]	
	,[KeepPrintedJobs]		
	,[LanguagesSupported]		
	,[LastErrorCode]			
	,[Local]				
	,[Location]			
	,[MarkingTechnology]		
	,[MaxCopies]			
	,[MaxNumberUp]			
	,[MaxSizeSupported]		
	,[MimeTypesSupported]		
	,[Name]				
	,[NaturalLanguagesSupported]	
	,[Network]			
	,[PaperSizesSupported]		
	,[PaperTypesAvailable]		
	,[Parameters]			
	,[PNPDeviceID]			
	,[PortName]			
	,[PowerManagementCapabilities]	
	,[PowerManagementSupported]	
	,[PrinterPaperNames]		
	,[PrinterState]			
	,[PrinterStatus]			
	,[PrintJobDataType]		
	,[PrintProcessor]		
	,[Priority]			
	,[Published]			
	,[Queued]			
	,[RawOnly]			
	,[SeparatorFile]			
	,[ServerName]			
	,[Shared]			
	,[ShareName]			
	,[SpoolEnabled]			
	,[StartTime]			
	,[Status]			
	,[StatusInfo]			
	,[SystemCreationClassName]	
	,[SystemName]			
	,[TimeOfLastReset]		
	,[UntilTime]			
	,[VerticalResolution]		
	,[WorkOffline]			
	
    ,[DateCreated]     	
	,DateModified = GETDATE()
  FROM [dbo].[vw_Win32_Printer]
  GO
  DROP VIEW [vw_Win32_Printer]
  GO