Category=Database Maintenance
Name=02.3. Migrate- ShelfInstance
Description=Grabs data from current VMR tables and imports it into the data model

OutputLevel=3

Heading=Import ShelfInstance table data from HSSListe table data (0 objects)
ImportSQL=		\
SELECT			\
 concat( cast( ObjID AS char ), InstID ) USERDATA,			\
 COALESCE(Adresse, 99999) INSTITUTIONID, 			\
 Shelf SHELFNUMBER, 			\
 COALESCE( Inhalt2, Inhalt ) CONTENTOVERVIEW, 			\
 Jh ORIGINYEARDESCRIPTION, 			\
 Blattzahl LEAVESDESCRIPTION,			\
 COALESCE(Blattzahl_Int, 0) LEAVES	\
 \
 FROM HSSListe where InstID='0' order by ObjID|			\
 INSERT INTO SHELFINSTANCE (			\
 \
 USERDATA,			\
 INSTITUTIONID,			\
 SHELFNUMBER, 			\
 CONTENTOVERVIEW, 		\
 ORIGINYEARDESCRIPTION,		\
 LEAVESDESCRIPTION,		\
 LEAVES				\
 ) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', {6})
 
 
Heading2=Import ShelfInstance table data from HSSListe table data (all but 0 and 00 objects)
ImportSQL2=		\
SELECT		\
concat( cast( ObjID AS char ), InstID ) USERDATA,		\
COALESCE(Adresse, 99999) INSTITUTIONID,		\
Shelf SHELFNUMBER,		\
(CASE WHEN GA<>T2.GANUMBER THEN GA ELSE null END) FORMERGANUMBER,		\
COALESCE( Inhalt2, Inhalt ) CONTENTOVERVIEW,		\
Jh ORIGINYEARDESCRIPTION, 		\
 Blattzahl LEAVESDESCRIPTION,			\
 COALESCE(Blattzahl_Int, 0) LEAVES	\
		\
FROM HSSListe T1		\
LEFT OUTER JOIN DOCUMENT T2 ON T2.DOCUMENTID=T1.ObjID		\
where InstID<>'00' and InstID<>'0' order by ObjID, InstID|		\
INSERT INTO SHELFINSTANCE (		\
		\
 USERDATA,		\
 INSTITUTIONID,		\
 SHELFNUMBER,		\
 FORMERGANUMBER,		\
 CONTENTOVERVIEW,		\
 ORIGINYEARDESCRIPTION,		\
 LEAVESDESCRIPTION,		\
 LEAVES				\
 )		\
 VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', {7})

Heading3=Setting all 99999 institution id's to unanswered (NULL)
SQL3=update SHELFINSTANCE set INSTITUTIONID=null where INSTITUTIONID=99999