parts.sql

print 'Running data script for table: dbo.AssetPartType'

-- declare table variable to hold desired data
declare @Data table
(
	ID int not null,
	Name varchar(50) not null,
    DisplayName nvarchar(50) not null,
    IsValidLeakLocation bit not null,
    IsValidServiceLocation bit not null,
    AssetTypeID int not null,
    ParentAssetPartTypeID int null,
    [Enabled] bit not null
)

-- populate the table variable with the desired data
insert into @Data
	(ID, Name, DisplayName, IsValidLeakLocation, IsValidServiceLocation, AssetTypeID, ParentAssetPartTypeID, Enabled)
values
--Compressor Parts
(1,'body/ terminal lugs','body/ terminal lugs',1,1,15,null,1),
(2,'fittings','fittings',1,1,15,null,1),
(3,'flanges/couplings','flanges/couplings',1,1,15,null,1),
(4,'head gasket','head gasket',1,1,15,null,1),
(5,'liqiuid injection valve','liqiuid injection valve',1,1,15,null,1),
(6,'oil float','oil float',1,1,15,null,1),
(7,'oil seperator / oil line','oil seperator / oil line',1,1,15,null,1),
(8,'pressure control transducer','pressure control transducer',1,1,15,null,1),
(9,'shraeder','shraeder',1,1,15,null,1),
(10,'shaft seal ','shaft seal ',1,1,15,null,1),
(11,'vibration eleimator','vibration eleimator',1,1,15,null,1),
(12,'gauge connection','gauge connection',1,1,15,null,1),
--Condensor
(13,'ball valve','ball valve',1,1,19,null,1),
(14,'coil','coil',1,1,19,null,1),
(15,'header / piping','header / piping',1,1,19,null,1),
(16,'pressure control tranducer','pressure control tranducer',1,1,19,null,1),
(17,'shraeder','shraeder',1,1,19,null,1),
(18,'splitting valve','splitting valve',1,1,19,null,1),
(19,'tube bundle(water cooled)','tube bundle(water cooled)',1,1,19,null,1),
(20,'manifold','manifold',1,1,19,null,1),
(21,'guage connection','guage connection',1,1,19,null,1),
--Discharge Liquid Line
(22,'ambient valve','ambient valve',1,1,1,69,1),
(23,'ball valve','ball valve',1,1,1,69,1),
(24,'check valve','check valve',1,1,1,69,1),
(25,'header','header',1,1,1,69,1),
(26,'heat reclaim coil','heat reclaim coil',1,1,1,69,1),
(27,'hot gas bypass ','hot gas bypass ',1,1,1,69,1),
(28,'muffler','muffler',1,1,1,69,1),
(29,'oil/speerator / rez','oil/speerator / rez',1,1,1,69,1),
(30,'piping','piping',1,1,1,69,1),
(31,'valves','valves',1,1,1,69,1),
(32,'pressure control ransducer','pressure control ransducer',1,1,1,69,1),
(33,'shraeder','shraeder',1,1,1,69,1),
(34,'pressure regulating valve','pressure regulating valve',1,1,1,69,1),
(35,'differential valve','differential valve',1,1,1,69,1),
(36,'drier','drier',1,1,1,69,1),
(37,'piping / header','piping / header',1,1,1,69,1),
(38,'pump','pump',1,1,1,69,1),
(39,'site glass','site glass',1,1,1,69,1),
(40,'sub cooler','sub cooler',1,1,1,69,1),
--Suction Line
(41,'accumulator','accumulator',1,1,1,68,1),
(42,'ball valve','ball valve',1,1,1,68,1),
(43,'CPR','CPR',1,1,1,68,1),
(44,'EPR','EPR',1,1,1,68,1),
(45,'Filter housing','Filter housing',1,1,1,68,1),
(46,'piping / header','piping / header',1,1,1,68,1),
(47,'pressure control transducer','pressure control transducer',1,1,1,68,1),
(48,'gauge','gauge',1,1,1,68,1),
(49,'shraeder','shraeder',1,1,1,68,1),
--Evaporator
(50,'ball valve','ball valve',1,1,18,null,1),
(51,'expansion device','expansion device',1,1,18,null,1),
(52,'coil','coil',1,1,18,null,1),
(53,'distributor','distributor',1,1,18,null,1),
(54,'piping','piping',1,1,18,null,1),
(55,'shraeder','shraeder',1,1,18,null,1),
(56,'secondary heat exchanger','secondary heat exchanger',1,1,18,null,1),
(57,'thermometer connection','thermometer connection',1,1,18,null,1),
--Heat Recovery
(58,'ball valve','ball valve',1,1,1,70,1),
(59,'expansion device','expansion device',1,1,1,70,1),
(60,'coil','coil',1,1,1,70,1),
(61,'distributor','distributor',1,1,1,70,1),
(62,'piping','piping',1,1,1,70,1),
--Receiver
(63,'king valve','king valve',1,1,1,71,1),
(64,'level indicator / alarm','level indicator / alarm',1,1,1,71,1),
(65,'pressure releif valve','pressure releif valve',1,1,1,71,1),
(66,'suction','suction',1,1,1,71,1),
(67,'discharge','discharge',1,1,1,71,1),
--System Level Parts
(68,'Suction Line', 'Suction Line',1, 1, 1, null,1),
(69,'Discharge / Liquid Line', 'Discharge / Liquid Line',1, 1, 1, null,1),
(70,'Heat Recovery', 'Heat Recovery',1, 1, 1, null,1),
(71,'Reciever', 'Reciever',1, 1, 1, null,1)
--insert missing rows

insert into dbo.AssetPartType
	(ID, Name, DisplayName, IsValidLeakLocation, IsValidServiceLocation, AssetTypeID, ParentAssetPartTypeID, Enabled)
select
	ID = temp.ID, 
	Name = temp.Name,
    DisplayName = temp.DisplayName,
    IsValidLeakLocation = temp.IsValidLeakLocation,
    IsValidServiceLocation = temp.IsValidServiceLocation,
    AssetTypeID = temp.AssetTypeID,
    ParentAsssetPartTypeID = temp.ParentAssetPartTypeID,
    Enabled = temp.Enabled
from @Data temp
left join dbo.AssetPartType live
	on live.ID = temp.ID
where
	live.ID is null	
-- update existing rows
update live
set
	Name = temp.Name,
    DisplayName = temp.DisplayName,
    IsValidLeakLocation = temp.IsValidLeakLocation,
    IsValidServiceLocation = temp.IsValidServiceLocation,
    AssetTypeID = temp.AssetTypeID,
    ParentAssetPartTypeID = temp.ParentAssetPartTypeID,
    Enabled = temp.Enabled
from dbo.AssetPartType live
inner join @Data temp
	on live.ID = temp.ID


go