900626 Asset Configuration and Tracking Consolidation : SqlQueriesFinal

This page last changed on Mar 14, 2007 by graybeal.

These queries have been tested against the SSDS_Metadata database. For queries configured for Northwind, see SqlQueries, but realize that those queries are somewhat older, and may work for John G but not others (because the tables were created for John G).

Finding Matching instruCopy Devices in Device (Information Only)

This finds full serial matches to Device mfgSerialNumber.

SELECT r2.MBARI_ID, d.id, r2.FullSerial, r2.Model, d.mfgModel, r2.Manufacturer, d.mfgName  
FROM ssdsdba.Device as d, 
(
SELECT * FROM
(
SELECT     *
FROM         instruCopy
WHERE     not exists (select null
FROM ssdsdba.Device
WHERE ssdsdba.Device.id = instruCopy.[SSDS ID] )
) r1
WHERE EXISTS (select null
FROM ssdsdba.Device
WHERE ssdsdba.Device.mfgSerialNumber = r1.FullSerial) 
) r2
WHERE d.mfgSerialNumber = FullSerial
ORDER BY r2.MBARI_ID

This finds short serial matches (lots of false positives).

SELECT r2.MBARI_ID, d.id, r2.Serial, r2.Model, d.mfgModel, r2.Manufacturer, d.mfgName  
FROM ssdsdba.Device as d, 
(
SELECT * FROM
(
SELECT     *
FROM         instruCopy
WHERE     not exists (select null
FROM ssdsdba.Device
WHERE ssdsdba.Device.id = instruCopy.[SSDS ID] )
) r1
WHERE EXISTS (select null
FROM ssdsdba.Device
WHERE ssdsdba.Device.mfgSerialNumber = r1.Serial) 
) r2
WHERE d.mfgSerialNumber = r2.Serial
ORDER BY r2.MBARI_ID

Match instruCopy to SSDS Devices

UPDATE instruCopy SET [SSDS ID] = 1359
WHERE MBARI_ID = 10

UPDATE instruCopy SET [SSDS ID] = 1567
WHERE MBARI_ID = 23

UPDATE instruCopy SET [SSDS ID] = 1568
WHERE MBARI_ID = 50

UPDATE instruCopy SET [SSDS ID] = 1222
WHERE MBARI_ID = 129

UPDATE instruCopy SET [SSDS ID] = 1273
WHERE MBARI_ID = 162

UPDATE instruCopy SET [SSDS ID] = 1337
WHERE MBARI_ID = 195

UPDATE instruCopy SET [SSDS ID] = 1279
WHERE MBARI_ID = 201

UPDATE instruCopy SET [SSDS ID] = 1356
WHERE MBARI_ID = 204

UPDATE instruCopy SET [SSDS ID] = 1470
WHERE MBARI_ID = 227

UPDATE instruCopy SET [SSDS ID] = 1562
WHERE MBARI_ID = 288

UPDATE instruCopy SET [SSDS ID] = 1382
WHERE MBARI_ID = 51

UPDATE instruCopy SET [SSDS ID] = 1406
WHERE MBARI_ID = 137

UPDATE instruCopy SET [SSDS ID] = 1267
WHERE MBARI_ID = 157

UPDATE instruCopy SET [SSDS ID] = 1456
WHERE MBARI_ID = 175

UPDATE instruCopy SET [SSDS ID] = 1457
WHERE MBARI_ID = 176

UPDATE instruCopy SET [SSDS ID] = 1314
WHERE MBARI_ID = 190

UPDATE instruCopy SET [SSDS ID] = 1459
WHERE MBARI_ID = 256

Insert Additional Devices in Device Type Column

Should be done before Instru merge.

/* Insert new devices */
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Backscatterometer/Fluorometer', 'backscatterometer that additionally provides fluorescence data')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Battery', 'device that stores energy and makes it available in an electrical form; not mediated by electronics (see Power Supply-Battery)')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Communication/GPS', 'transmits signals from one place to another, and contains GPS locator')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Echo Sounder-Singlebeam', 'acoustic system which transmits short acoustic pulses from single transmitter' +
            ' into the water column, and then detects echoes from impedance discontinuities (typically fish or plankton)')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Echo Sounder-Multibeam', 'acoustic system which transmits short acoustic pulses from multiple transmitters' +
            ' into the water column, and then detects echoes from impedance discontinuities (typically from the benthic layer)')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Inductive Modem Cable Coupler', 'connector for indcutive modems (ICC)')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Power Supply-Electric', 'power supply (delivers power to other equipment) that does not store energy')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Pump', 'mechanical device used to move liquids or gases')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Release', 'mechanism to disconnect two components, typically allowing a buoyant component in water'+
                   ' to detach from an anchor and float to the surface')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Sonar-Sidescan Swath Bathymetry', 'fixed-mounted acoustic bathymetric sounding systems that sweep a wide path; also known as SSBS')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Sonar-Scanning', 'acoustic system that can move (examples are Articulating, Profiling, and Pencil Beam)')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Sonar-Subbottom', 'acoustic system designed to penetrate layers beneath the benthic surface (e.g., CHIRP)')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Transmissometer', 'device to measure the optical transparency of water (also turbidity sensor)')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Transponder', 'electrical device designed to receive a specific signal and automatically transmit a specific reply')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Test Entry-System', 'entry created for purpose of testing the system')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Not Specified-Unknown', 'an actual device for which the device type is not known (and not knowable)')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Not Specified-TBD', 'entry for which the device type will be provided later')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Not Specified-Undefined', 'used for a device entry which does not yet define a specific device')
INSERT INTO ssdsdba.DeviceType(version ,Name, Description)
VALUES(0, 'Controller-CTD', 'controls CTD data acquisition')

Update Deprecated Device Descriptions

These statements fix the newly added Device Descriptions (for the deprecated device types).

UPDATE ssdsdba.DeviceType
SET description='Deprecated, please choose another type'
WHERE description is null

Update Nephelometer

Fix a spelling mistake.

/* Update the nephelometer spelling in DeviceType table */
UPDATE ssdsdba.DeviceType
set name='Fluorometer/Nephelometer', description='fluorometer configured to also do nephelometry (turbidity sensing)'
where name LIKE '%Fluor%Nep%'

Create Table of Legal Manufacturer Names

This isn't needed until later, but we're getting it out of the way now.

CREATE TABLE Manufacturers
 (id int IDENTITY PRIMARY KEY,
  label varchar (100) NOT NULL,
  fullName varchar(100) NOT NULL, 
  oldNames varchar(250) NULL,
  comments varchar(250) NULL)
GO

INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Aanderaa Data Instruments','Aanderaa Data Instruments','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Axys Technologies','Axys Technologies Inc.','Axys Environmental Systems','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Biospherical Instruments','Biospherical Instruments Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Bluefin Robotics','Bluefin Robotics Corporation','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Brook Ocean Technology','Brook Ocean Technology Limited','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Crossbow Technology','Crossbow Technology Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Garmin','Garmin','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('HOBI Labs','Hydro-Optics, Biology, & Instrumentation Laboratories','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Magellan Navigation','Magellan Navigation, Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('MBARI','Monterey Bay Aquarium Research Institute','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('McLane Research Laboratories','McLane Research Laboratories, Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('NOBSKA','NOBSKA','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Nortek AS','Nortek AS','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('ORBCOMM','ORBCOMM, Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Paroscientific','Paroscientific, Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('QUAKE Global','QUAKE Global, Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Satlantic','Satlantic Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Sea-Bird Electronics','Sea-Bird Electronics, Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('SeaTech','SeaTech, Inc.','','no longer exists')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Simrad','Simrad, Inc','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Star Engineering for WHOI-Asimet','Star Engineering','',
                                                                         'Star Engineering is only authorized seller of ASIMETs')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('tbd','to be determined value for manufacturer','','if totally unknown use unknown')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Teledyne Benthos','Teledyne Benthos','Benthos','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Teledyne RD Instruments','Teledyne RD Instruments','RD Instruments | RDI',''	)
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('testing','test entry','','use for test entries')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('UC Santa Barbara','University of California, Santa Barbara','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('unknown','unknown manufacturer','','use for permanently unknown values, else use tbd')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('WET Labs','WET Labs','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('WHOI','Woods Hole Oceanographic Institution','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Xantrex Technology','Xantrex Technology Inc.','','')

Add New Device Table Entries from instruCopy

This script creates new Device entries for the instru devices not already in SSDS.

Double-check for Erich R's name in the Technician field before setting everything to 126.
Kevin: Paul says he sees Erich's name in the Technician field, but I just copied the latest instru from BOG and I don't see it. Can you resolve this apparent conflict? – jbg 3/14 6 PM.

Note this is updated now to set the device type to an actual entry.
This fails if the DeviceType table has not been updated.

/** Copy all instruCopy devices without SSDS IDs **/
INSERT INTO [SSDS_Metadata].[ssdsdba].[Device]
([version], [uuid], [name], [description], [mfgName], [mfgModel], [mfgSerialNumber], 
[infoUrlList], [PersonID_FK], [DeviceTypeID_FK], [Serial], 
[Calibration_organization], [Features], [Pressure_sensor], [Depth_Rating], 
[Firmware_EPROM], [Memory], [Receive_frequency], [Transmit_frequency], 
[Enable_code], [Release_code], [Tilt_option], [Purchased_for], 
[Owner], [Custodian], [Date_new], [PO], [Transaction_col], 
[Permanent_comment], [document_dir], [osg_view], [MBARI_ID])

  /** Notes for following:
  Set Version, UUID to 0
  Set name to null and description to Mfg + Model
  * Leaving DeviceType as the TBD entry, to fix up later
  * mfgSerialNumber gets Serial; overwrite with FullSerial (if exists) later in scripts
  Transaction_col is set to null (always null anyway)
  * Leaving MBARI_ID unchanged for now; later can set it to id
  **/
  SELECT 
  0,NEWID(),null,i.Manufacturer + ' ' + i.Model , i.Manufacturer, i.Model, i.Serial, 
  CAST (i.[manufacture web page]AS varchar(2048)), 126, (select id from ssdsdba.DeviceType where name LIKE '%TBD%'), i.Serial,
  i.[Calibration organization], i.Features, i.[Pressure sensor], i.[Depth Rating],
  i.[Firmware/EPROM], i.Memory, i.[Receive frequency], i.[Transmit frequency],
  i.[Enable code], i.[Release code], i.[Tilt option], i.[Purchased for],
  i.Owner, i.Custodian, i.[Date new], i.PO, null,
  i.[Permanent comment], i.[document dir], 1, i.MBARI_ID
  
  FROM instruCopy as i
  WHERE i.[SSDS ID] is null OR i.[SSDS ID] = 0

/* Copy SSDS ID for new devices back into the SSDS ID column of instruCopy. */
UPDATE    i
  SET              [SSDS ID] = d.id
  FROM ssdsdba.Device d, instruCopy i
  WHERE      d.MBARI_ID = i.MBARI_ID AND (i.[SSDS ID] is null OR i.[SSDS ID] = 0)

UPDATE ssdsdba.Device Table Entries from instruCopy Devices

This updates the Device table for the instru entries that we know are SSDS entries.

/** Update all instruCopy devices that have SSDS IDs **/
UPDATE    d
SET      
/* Don't overwrite the model in Device if instruCopy model is null */       
d.mfgModel = 
  case when i.Model is not null then i.Model else d.mfgModel end,
d.mfgSerialNumber = i.Serial,
d.infoUrlList = CAST (i.[manufacture web page]AS varchar(2048)),
d.PersonID_FK = 126,
d.Serial = i.Serial,
d.Calibration_organization = i.[Calibration organization],
d.Features = i.Features,
d.Pressure_sensor = i.[Pressure sensor],
d.Depth_Rating = i.[Depth Rating],
d.Firmware_EPROM = i.[Firmware/EPROM],
d.Memory = i.Memory,
d.Receive_frequency = i.[Receive frequency],
d.Transmit_frequency = i.[Transmit frequency],
d.Enable_code = i.[Enable code],
d.Release_code = i.[Release code],
d.Tilt_option = i.[Tilt option],
d.Purchased_for = i.[Purchased for],
d.Owner = i.Owner,
d.Custodian = i.Custodian,
d.Date_new = i.[Date new],
d.PO = i.PO,
d.Transaction_col = null,
d.Permanent_comment = i.[Permanent comment],
d.document_dir = i.[document dir],
d.osg_view = 1,
d.MBARI_ID = i.MBARI_ID
FROM ssdsdba.Device d, instruCopy i
/* If osg_view = 1 then this device was created in the steps above, no need to recopy. */
WHERE      d.id = i.[SSDS ID] AND d.osg_view = 0

Update mfgSerialNumber to Full Serial from instruCopy

The short serial was copied in the merge step above, if it existed. This overwrites with FullSerial from instru, if it exists.
This version corrects a bug from the Northwind version, where it only tested for MBARI_IDs matching.

/* Update mfgSerialNmber column with any FullSerial value */
UPDATE    d
SET              mfgSerialNumber = i.[FullSerial]
FROM ssdsdba.Device d, instruCopy i
WHERE      d.id = i.[SSDS ID] 
AND NOT (i.FullSerial is null OR i.FullSerial = '')

Create an Interim Working Table

This makes the TempDevFld table, which is used to cache completed mfgName and deviceType data.

/** Make Interim table **/
DROP TABLE TempDevFld
CREATE TABLE TempDevFld
 (id int PRIMARY KEY,
  tempMfg varchar (255) NULL,
  tempType varchar (255) NULL
 )
GO
/* Populate with all the device IDs */
INSERT INTO TempDevFld
  (id)
SELECT d.id 
FROM ssdsdba.Device as d

Update Manufacturer (mfgName)

Makes the Manufacturer names consistent.

/** Update manufacturer names to ones in Manufacturers table **/
/* Grab names from instruCopy that match exactly */
UPDATE    t
SET       t.tempMfg = i.Manufacturer
FROM      TempDevFld t, instruCopy i
WHERE      t.id = i.[SSDS ID] and i.Manufacturer in (SELECT label from Manufacturers) AND t.tempMfg is null 
/* Grab names from Device that match exactly */
UPDATE    t
SET       t.tempMfg = d.mfgName
FROM      TempDevFld t, ssdsdba.Device d
WHERE      t.id = d.id and d.MfgName in (SELECT label from Manufacturers) AND t.tempMfg is null 

/* Find names that are like the correct names */
/* First from instruCopy */
UPDATE    t 
SET       t.tempMfg = r1.label 
FROM TempDevFld t, 
(     
  SELECT t.id as tid, t.tempMfg, i.[SSDS ID] as iid, i.Manufacturer, m.label
    FROM TempDevFld t JOIN instruCopy i
      ON (t.id = i.[SSDS ID]), Manufacturers m
  WHERE t.tempMfg is null AND
     ((substring(i.Manufacturer,1,3) = substring(m.label,1,3) 
            AND i.Manufacturer <> m.label 
            AND i.Manufacturer <> 'SeaTech' AND m.label <> 'SeaTech') OR
         CHARINDEX(i.Manufacturer,m.label) > 1)
) r1
WHERE t.id = r1.tid AND t.tempMfg is null

/* Then from Device */
UPDATE    t 
SET       t.tempMfg = r1.label 
FROM TempDevFld t, 
(     
  SELECT t.id as tid, t.tempMfg, d.id as did, d.mfgName, m.label
    FROM TempDevFld t JOIN ssdsdba.Device d
      ON (t.id = d.id), Manufacturers m
  WHERE t.tempMfg is null AND
     ((substring(d.mfgName,1,3) = substring(m.label,1,3) AND (d.mfgName <> m.label) 
            AND d.mfgName <> 'SeaTech' AND m.label <> 'SeaTech') OR
         CHARINDEX(d.mfgName,m.label) > 1)
) r1
WHERE t.id = r1.tid AND t.tempMfg is null

/* Copy the results back to Device table */
UPDATE d
SET d.MfgName = t.tempMfg
FROM ssdsdba.Device d, TempDevFld t
WHERE d.id = t.id AND t.tempMfg is not null

/* Do manual corrections */
/** Manual MfgName -- we now update in the Device table directly **/

/* This appears to have no effect, item likely corrected in solstice Device table */
UPDATE ssdsdba.Device
SET mfgName = 'Aanderaa Instruments'
WHERE mfgName = 'Anderraa Instruments'

UPDATE ssdsdba.Device
SET mfgName = 
'Magellan Navigation'
WHERE mfgName LIKE
'Ashtech%'

UPDATE ssdsdba.Device
SET mfgName = 
'Axys Technologies'
WHERE mfgName LIKE
'AXYS Technologies'

UPDATE ssdsdba.Device
SET mfgName = 
'Star Engineering for WHOI-Asimet'
WHERE mfgName LIKE
'Asimet%'

/* Pick up some ASIMETs that are wrong in Device */
UPDATE ssdsdba.Device
SET mfgName = 
'Star Engineering for WHOI-Asimet'
WHERE id = 1250 OR id = 1251

UPDATE ssdsdba.Device
SET mfgName = 
'AXYS Technologies'
WHERE mfgName LIKE
'Triaxys%'

UPDATE ssdsdba.Device
SET mfgName = 
'Teledyne RD Instruments'
WHERE mfgName =
'RDI'

UPDATE ssdsdba.Device
SET mfgName = 
'UC Santa Barbara'
WHERE mfgName =
'UCSB'

UPDATE ssdsdba.Device 
SET mfgName =
'testing'
WHERE mfgName LIKE
'Fraben%'

For information only, this report generates a list of the original mfgName/Manufacturer against the newly created tempMfg. It would have to be run before copying the data back to the Device table above, and would not include the final adjustments shown below that.

/** Get relevant ID and manufacturer information from 3 tables **/
SELECT t.tempMfg, d.mfgName, i.Manufacturer, t.id, d.id, i.[SSDS ID], i.MBARI_ID
FROM 
  (
  TempDevFld t 
  FULL OUTER JOIN ssdsdba.Device d 
    ON t.id = d.id 
  )
  FULL OUTER JOIN instruCopy i
    ON i.[SSDS ID] = t.id
ORDER BY tempMfg, mfgName

Update Model (mfgModel)

mfgModel column already contains BOG Model if any existed; this was
done in initial merge of the two tables.

/* Update mfgModel to reflect manual corrections */
UPDATE ssdsdba.Device
SET mfgModel = 
'GPC16-HVS'
WHERE id = 1313

UPDATE ssdsdba.Device
SET mfgModel = 
'ECO FLNTUSB'
WHERE id = 1395

UPDATE ssdsdba.Device
SET mfgModel = 
null
WHERE mfgModel =
'(null)'

UPDATE ssdsdba.Device
SET mfgModel = 
replace (mfgModel, '-', ' ')
WHERE mfgModel LIKE 'ECO-FLNT%'

UPDATE ssdsdba.Device
SET mfgModel = 
'ECO Triplet'
WHERE mfgModel =
'triplet'

UPDATE ssdsdba.Device
SET mfgModel = 
'MMCv4'
WHERE mfgModel LIKE
'MMC v%'

UPDATE ssdsdba.Device
SET mfgModel = 
'Medusa Rev 2'
WHERE mfgModel LIKE
'Medusa R%'


UPDATE ssdsdba.Device
SET mfgModel = 
'Medusa Rev 2'
WHERE mfgModel LIKE
'Medusa v%'

UPDATE ssdsdba.Device
SET mfgModel = 
'OASIS Buoy'
WHERE mfgModel LIKE
'Fiberglass tor%'

UPDATE ssdsdba.Device
SET mfgModel = 
'E-meter'
WHERE mfgModel =
'Emeter'

UPDATE temporary device type Column by Merging

This works on the temporary table TempDevFld.

/* UPDATE temporary Device Type column */
/* Grab names from Device that already exist (but do it through view) */
UPDATE    t
SET       t.tempType = dv.devType
FROM      TempDevFld t, devTypeView dv
WHERE      t.id = dv.id AND dv.devType is not null AND t.tempType is null

/* Change instruCopy entries to be 'right' */
UPDATE i
SET i.Type = 'Controller-Mooring Node'
FROM instruCopy i
WHERE i.Type = 'Controller' AND (i.Model LIKE 'OASIS%' OR i.Model = 'MMC')

UPDATE i
SET i.Type = 'backscatterometer'
FROM instruCopy i
WHERE Type = 'backscatter' AND (Model ='HS2' OR Model='VSFS')

UPDATE i
SET i.Type = 'scatterometer'
FROM instruCopy i
WHERE Type = 'backscatter' AND Model ='ECO BBSB'

UPDATE i
SET i.Type = 'Inductive Modem-Surface'
FROM instruCopy i
WHERE Type = 'modem' AND Model ='SIM'

UPDATE i
SET i.Type = 'Fluorometer/Nephelometer'
FROM instruCopy i
WHERE Type = 'fluor/turbidity' 

UPDATE i
SET i.Type = 'Backscatterometer/Fluorometer'
FROM instruCopy i
WHERE Type = 'backscatter/fluorometer' 

UPDATE i
SET i.Type = 'Metereology Package'
FROM instruCopy i
WHERE Type = 'temperature/humidity' 

UPDATE i
SET i.Type = 'Nitrogen Sensor/ISUS'
FROM instruCopy i
WHERE Type = 'Nitrate' 

UPDATE i
SET i.Type = 'Nitrogen Sensor (this is Satlantic unit)'
FROM instruCopy i
WHERE Type = 'Nitrate analyzer' 

UPDATE i
SET i.Type = 'Oxygen Sensor'
FROM instruCopy i
WHERE Type = 'Oxygen optode' 

UPDATE i
SET i.Type = 'Toroid'
FROM instruCopy i
WHERE Type = 'Platform' 

UPDATE i
SET i.Type = 'Power Supply-Electric'
FROM instruCopy i
WHERE Type = 'power source' 

UPDATE i
SET i.Type = 'CO2 Monitor'
FROM instruCopy i
WHERE Type = 'pCO2' 

UPDATE i
SET i.Type = 'Transponder'
FROM instruCopy i
WHERE Type = 'transducer' 

UPDATE i
SET i.Type = 'Echo Sounder-Singlebeam'
FROM instruCopy i
WHERE Type = 'sounder' 

UPDATE i
SET i.Type = 'Shutter-Antifouling'
FROM instruCopy i
WHERE Type = 'shutter' 

UPDATE i
SET i.Type = 'Inductive Modem Cable Coupler'
FROM instruCopy i
WHERE Type = 'ICC' 

UPDATE i
SET i.Type = 'Pump'
FROM instruCopy i
WHERE Type = 'pump' 

UPDATE i
SET i.Type = 'Release'
FROM instruCopy i
WHERE Type = 'release' 

UPDATE i
SET i.Type = 'Backscatterometer'
FROM instruCopy i
WHERE Type = 'backscatter' OR Type = 'backscatterometer'

UPDATE i
SET i.Type = 'Battery'
FROM instruCopy i
WHERE Type = 'battery' 

UPDATE i
SET i.Type = 'Transmissometer'
FROM instruCopy i
WHERE Type = 'transmissometer' 


/* Find matching instruCopy names */
/* Grab names from instruCopy that match exactly */
UPDATE    t
SET       t.tempType = i.Type
FROM      TempDevFld t, instruCopy i
WHERE      t.id = i.[SSDS ID]
          AND i.Type in (SELECT devType from devTypeView) 
          AND (t.tempType is null OR t.tempType = 'Not Specified-TBD')

Manual Device Type Fixes

This updaets all the errant device types (still in the temporary table).

UPDATE    t
SET       t.tempType = 'Radiometer-Multispectral'
FROM      TempDevFld t
WHERE      t.id = 1381
UPDATE    t
SET       t.tempType = 'Inductive Modem-Surface'
FROM      TempDevFld t
WHERE      t.id = 1319
UPDATE    t
SET       t.tempType = 'Controller-CTD'
FROM      TempDevFld t
WHERE      t.id = 1224
UPDATE    t
SET       t.tempType = 'Communication/GPS'
FROM      TempDevFld t
WHERE      t.id = 1323
UPDATE    t
SET       t.tempType = 'Currents Sensor-ADCP'
FROM      TempDevFld t
WHERE     t.tempType = 'ADCP'

The final copy of device information back into the Device table (through the view, so it uses the FKs).

UPDATE d2
SET d2.DeviceTypeID_FK = r1.typeid
FROM ssdsdba.Device d2, 
(
select t.id as id, d.id as typeid 
from TempDevFld t left outer join ssdsdba.DeviceType d 
on t.tempType = d.name 
) r1
WHERE d2.id = r1.id

For information only, here is code to look at the final device types to see if they are sensible.

/* Final check to see if it's all working */

select d.id, dtv.id, dtv.devType, d.name, d.mfgName, d.mfgModel, d.description  
from
devTypeView as dtv JOIN ssdsdba.Device as d
on dtv.id = d.id
order by devType, d.id