gistfile1.txt

CREATE TABLE `ingress` (
  `ing_index` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `mrn` varchar(9) DEFAULT NULL,
  `originalvial` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`ing_index`)
) ENGINE=InnoDB AUTO_INCREMENT=187434 DEFAULT CHARSET=utf8;

CREATE TABLE `norminv` (
  `pni_index` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `mrn` varchar(9) DEFAULT NULL,
  `originalvial` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pni_index`),
  KEY `idx_mrn` (`mrn`)
) ENGINE=InnoDB AUTO_INCREMENT=171777 DEFAULT CHARSET=utf8;

CREATE TABLE `tracking` (
  `tracking_index` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `fpni` bigint(20) unsigned DEFAULT NULL,
  `f_ing_index` bigint(20) unsigned DEFAULT NULL,
  `f_req_index` bigint(20) unsigned DEFAULT NULL,
  `last_modified` datetime DEFAULT NULL,
  `modified_by` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`tracking_index`)
) ENGINE=InnoDB AUTO_INCREMENT=71467 DEFAULT CHARSET=utf8;


DELIMITER $$
CREATE DEFINER=`tillabuser`@`%` PROCEDURE `MergeIngPendingToNorminv`()
BEGIN

UPDATE `til_lab`.`ingress`
SET location = 'freeze box'
WHERE
  (location IS NULL OR location = '')
  AND
  (vialnum IS NOT NULL OR vialnum > 0);

INSERT INTO `til_lab`.`norminv`
 (SELECT null, mrn, null, capcolor, datefrzn, cellid, vialnum,
  amount, units, location, comments, originalvial
 FROM `til_lab`.`ingress`
 WHERE
     mrn IS NOT NULL
   AND
     mrn <> ''
   AND
     cellid IS NOT NULL
   AND
     cellid <> ''
   AND
     vialnum IS NOT NULL
   AND
     dateVerified IS NULL
 );

UPDATE ingress
SET dateVerified = CURDATE()
 WHERE
     mrn IS NOT NULL
   AND
     mrn <> ''
   AND
     cellid IS NOT NULL
   AND
     cellid <> ''
   AND
     vialnum IS NOT NULL
   AND
     dateVerified IS NULL;


END$$
DELIMITER ;

DECLARE @vials TABLE (Id INT IDENTITY(1,1), VialDetails NCHAR(1))
DECLARE @Batches TABLE (BatchId INT IDENTITY(1,1), VialId INT, BatchDetails NCHAR(1))

DECLARE @vials TABLE (VialId INT IDENTITY(1,1), VialDetails NCHAR(1))
DECLARE @Batches TABLE (BatchId INT IDENTITY(1,1), VialId INT, BatchDetails NCHAR(1))