Project

General

Profile

Revision eb191b81

IDeb191b81de72da546716c0328d6fdedbffbf4660
Parent cac02f6a
Child be2b30e1

Added by Andreas Müller over 9 years ago

update for check master

View differences:

cdm-pesi/src/main/resources/sql/Check_Import_Master.sql
306 306
	ISNULL(bm_t.TaxonStatusCache, '') <> ISNULL(cdm_t.TaxonStatusCache, '')
307 307
	*/
308 308

  
309
-- Parents
310
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE ParentTaxonFk IS NOT NULL
311
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE ParentTaxonFk IS NOT NULL
312
	SET @n = @n_bm - @n_cdm
313

  
314
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
315
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
316
	SET @str_n = Cast(@n AS NVARCHAR)
317

  
318
	IF @n = 0 BEGIN
319
		PRINT ('Both databases have the same number of taxa which have a parent = ' + @str_n_bm)
320
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa that have a parent, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
321

  
322
		SELECT @n = COUNT(*) -- in both databases parents exist but are different
323
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
324
			[CDM_EM2PESI].[DBO].TAXON cdm_t
325
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
326
			[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId INNER JOIN
327
			[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
328
			WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
329
		SET @str_n = Cast(@n AS NVARCHAR)
330
		IF @n = 0 BEGIN
331
			PRINT ('All identical taxa that have parents have the same parent')
332
		END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa have different parents')
333
	/*
334
		SELECT  cdm_t.FullName as Child_CDM, cdm_pt.Fullname as Parent_CDM_EM2PESI, bm_t.FullName as child_sql,  bm_pt.Fullname as Parent_EM2PESI
335
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
336
			[CDM_EM2PESI].[DBO].TAXON cdm_t ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
337
			[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId INNER JOIN
338
		[	CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
339
		WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
340
	*/
341
		SELECT @n_bm = COUNT(*) -- taxa with parent only in EM2PESI
342
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
343
			[CDM_EM2PESI].[DBO].TAXON cdm_t
344
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
345
			[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId
346
			WHERE NOT EXISTS
347
				(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON
348
				WHERE IdInSource = bm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_pt.GUID, ''))
349
		SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
350
		IF @n_bm > 0 BEGIN 
351
			PRINT ('WARNING: ' + @str_n_bm + ' identical taxa have parents in EM2PESI but not in CDM_EM2PESI')
352
		END ELSE PRINT ('All identical taxa that have parents in EM2PESI do have parents in CDM_EM2PESI')
353

  
354
	/*
355
		SELECT cdm_t.Fullname ChildName, bm_pt.Fullname as Parent_EM2PESI, 'in EM2PESI but not in CDM_EM2PESI'
356
		FROM [EM2PESI].[DBO].TAXON bm_t 
357
			INNER JOIN [EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId
358
			INNER JOIN [CDM_EM2PESI].[DBO].TAXON cdm_t ON ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') 
359
		
360
		WHERE cdm_t.ParentTaxonFk  IS NULL AND NOT EXISTS 
361
		(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON cdm_pt
362
		WHERE (cdm_pt.IdInSource = bm_pt.IdInSource OR cdm_pt.IdInSource IS NULL AND bm_pt.IdInSource IS NULL) 
363
				AND ISNULL(cdm_pt.GUID, '') = ISNULL(bm_pt.GUID, '')
364
				AND cdm_t.ParentTaxonFk = cdm_pt.TaxonId)
365
		ORDER BY cdm_t.Fullname
366

  
367
	*/
368
		SELECT @n_cdm = COUNT(*) -- taxa with parent only in CDM_EM2PESI
369
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
370
			[CDM_EM2PESI].[DBO].TAXON cdm_t
371
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
372
			[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
373
			WHERE NOT EXISTS
374
				(SELECT * FROM [EM2PESI].[DBO].TAXON
375
				WHERE IdInSource = cdm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_pt.GUID, ''))
376
		SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
377
		IF @n_cdm > 0 BEGIN 
378
			PRINT ('WARNING: ' + @str_n_cdm + ' identical taxa have parents in CDM_EM2PESI but not in EM2PESI')
379
		END ELSE PRINT('All identical taxa that have parents in CDM_EM2PESI do have parents in EM2PESI')
380
		
381
	/*
382
		SELECT bm_t.Fullname ChildName, cdm_pt.Fullname as Parent_CDM, 'in CDM_EM2PESI but not in EM2PESI'
383
		FROM [CDM_EM2PESI].[DBO].TAXON cdm_t 
384
			INNER JOIN [CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
385
			INNER JOIN [EM2PESI].[DBO].TAXON bm_t ON ISNULL(cdm_t.GUID, '') = ISNULL(bm_t.GUID, '') 
386
		
387
		WHERE bm_t.ParentTaxonFk  IS NULL AND NOT EXISTS 
388
		(SELECT * FROM [EM2PESI].[DBO].TAXON bm_pt
389
		WHERE (bm_pt.IdInSource = cdm_pt.IdInSource OR bm_pt.IdInSource IS NULL AND cdm_pt.IdInSource IS NULL) 
390
				AND ISNULL(bm_pt.GUID, '') = ISNULL(cdm_pt.GUID, '')
391
				AND bm_t.ParentTaxonFk = bm_pt.TaxonId)
392
		ORDER BY bm_t.Fullname
393
	*/
394
		IF @n = 0 AND @n_cdm = 0 AND @n_bm = 0 BEGIN
395
			PRINT ('All identical taxa have the same parent')
396
		END
397

  
398
-- TreeIndex
399
/* This is not checked. This field should be created by the PESI-Procedure recalculateallstoredpaths.
400
	Actually checking the parents of the same taxa is enough to ensure the compatibility of the taxonomical tree as a whole.
401
	See the Parent section
402
*/
403
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE TreeIndex IS NOT NULL
404
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE TreeIndex IS NOT NULL
405
	SET @n = @n_bm - @n_cdm
406

  
407
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
408
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
409
	SET @str_n = Cast(@n AS NVARCHAR)
410

  
411
	IF @n = 0 BEGIN
412
		PRINT ('Both databases have the same number of taxa which have a tree index = ' + @str_n_bm)
413
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa that have a tree index, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
414

  
415

  
416 309
-- Types
417 310
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE TypeNameFk IS NOT NULL
418 311
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE TypeNameFk IS NOT NULL
......
590 483
	convert(smalldatetime, ISNULL(cdm_t.LastActionDate, '00:00:00'))
591 484
	*/
592 485

  
486

  
487
	
488
-- Parents
489

  
490
	PRINT ' '
491
	PRINT 'PARENTS'
492
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE ParentTaxonFk IS NOT NULL
493
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE ParentTaxonFk IS NOT NULL
494
	SET @n = @n_bm - @n_cdm
495

  
496
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
497
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
498
	SET @str_n = Cast(@n AS NVARCHAR)
499

  
500
	IF @n = 0 BEGIN
501
		PRINT ('Both databases have the same number of taxa which have a parent = ' + @str_n_bm)
502
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa that have a parent, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
503

  
504
		SELECT @n = COUNT(*) -- in both databases parents exist but are different
505
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
506
			[CDM_EM2PESI].[DBO].TAXON cdm_t
507
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
508
			[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId INNER JOIN
509
			[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
510
			WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
511
		SET @str_n = Cast(@n AS NVARCHAR)
512
		IF @n = 0 BEGIN
513
			PRINT ('All identical taxa that have parents have the same parent')
514
		END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa have different parents')
515
	/*
516
		SELECT  cdm_t.FullName as Child_CDM, cdm_pt.Fullname as Parent_CDM_EM2PESI, bm_t.FullName as child_sql,  bm_pt.Fullname as Parent_EM2PESI
517
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
518
			[CDM_EM2PESI].[DBO].TAXON cdm_t ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
519
			[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId INNER JOIN
520
		[	CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
521
		WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
522
	*/
523
		SELECT @n_bm = COUNT(*) -- taxa with parent only in EM2PESI
524
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
525
			[CDM_EM2PESI].[DBO].TAXON cdm_t
526
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
527
			[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId
528
			WHERE NOT EXISTS
529
				(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON
530
				WHERE IdInSource = bm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_pt.GUID, ''))
531
		SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
532
		IF @n_bm > 0 BEGIN 
533
			PRINT ('WARNING: ' + @str_n_bm + ' identical taxa have parents in EM2PESI but not in CDM_EM2PESI')
534
		END ELSE PRINT ('All identical taxa that have parents in EM2PESI do have parents in CDM_EM2PESI')
535

  
536
	/*
537
		SELECT cdm_t.Fullname ChildName, bm_pt.Fullname as Parent_EM2PESI, 'in EM2PESI but not in CDM_EM2PESI'
538
		FROM [EM2PESI].[DBO].TAXON bm_t 
539
			INNER JOIN [EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId
540
			INNER JOIN [CDM_EM2PESI].[DBO].TAXON cdm_t ON ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') 
541
		
542
		WHERE cdm_t.ParentTaxonFk  IS NULL AND NOT EXISTS 
543
		(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON cdm_pt
544
		WHERE (cdm_pt.IdInSource = bm_pt.IdInSource OR cdm_pt.IdInSource IS NULL AND bm_pt.IdInSource IS NULL) 
545
				AND ISNULL(cdm_pt.GUID, '') = ISNULL(bm_pt.GUID, '')
546
				AND cdm_t.ParentTaxonFk = cdm_pt.TaxonId)
547
		ORDER BY cdm_t.Fullname
548

  
549
	*/
550
		SELECT @n_cdm = COUNT(*) -- taxa with parent only in CDM_EM2PESI
551
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
552
			[CDM_EM2PESI].[DBO].TAXON cdm_t
553
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
554
			[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
555
			WHERE NOT EXISTS
556
				(SELECT * FROM [EM2PESI].[DBO].TAXON
557
				WHERE IdInSource = cdm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_pt.GUID, ''))
558
		SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
559
		IF @n_cdm > 0 BEGIN 
560
			PRINT ('WARNING: ' + @str_n_cdm + ' identical taxa have parents in CDM_EM2PESI but not in EM2PESI')
561
		END ELSE PRINT('All identical taxa that have parents in CDM_EM2PESI do have parents in EM2PESI')
562
		
563
	/*
564
		SELECT bm_t.Fullname ChildName, cdm_pt.Fullname as Parent_CDM, 'in CDM_EM2PESI but not in EM2PESI'
565
		FROM [CDM_EM2PESI].[DBO].TAXON cdm_t 
566
			INNER JOIN [CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
567
			INNER JOIN [EM2PESI].[DBO].TAXON bm_t ON ISNULL(cdm_t.GUID, '') = ISNULL(bm_t.GUID, '') 
568
		
569
		WHERE bm_t.ParentTaxonFk  IS NULL AND NOT EXISTS 
570
		(SELECT * FROM [EM2PESI].[DBO].TAXON bm_pt
571
		WHERE (bm_pt.IdInSource = cdm_pt.IdInSource OR bm_pt.IdInSource IS NULL AND cdm_pt.IdInSource IS NULL) 
572
				AND ISNULL(bm_pt.GUID, '') = ISNULL(cdm_pt.GUID, '')
573
				AND bm_t.ParentTaxonFk = bm_pt.TaxonId)
574
		ORDER BY bm_t.Fullname
575
	*/
576
		IF @n = 0 AND @n_cdm = 0 AND @n_bm = 0 BEGIN
577
			PRINT ('All identical taxa have the same parent')
578
		END
579

  
580
-- TreeIndex
581
/* This is not checked. This field should be created by the PESI-Procedure recalculateallstoredpaths.
582
	Actually checking the parents of the same taxa is enough to ensure the compatibility of the taxonomical tree as a whole.
583
	See the Parent section
584
*/
585
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE TreeIndex IS NOT NULL
586
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE TreeIndex IS NOT NULL
587
	SET @n = @n_bm - @n_cdm
588

  
589
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
590
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
591
	SET @str_n = Cast(@n AS NVARCHAR)
592

  
593
	IF @n = 0 BEGIN
594
		PRINT ('Both databases have the same number of taxa which have a tree index = ' + @str_n_bm)
595
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa that have a tree index, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
596
	
593 597
------------------------------------------- RelTaxon -------------------------------
594 598
	PRINT ' '
595 599
	PRINT 'RELATIONSHIP'
......
795 799

  
796 800
------------------------------------------- AdditionalTaxonSource -------------------------------
797 801
	PRINT ' '
798
	PRINT 'AdditionalTaxonSource'
802
	PRINT 'ADDITIONAL TAXON SOURCE'
799 803
	
800 804
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].AdditionalTaxonSource
801 805
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource
......
816 820
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
817 821
			WHERE NOT EXISTS
818 822
			(SELECT * FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource
819
			WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(SourceUseCache,'') = ISNULL(bm_ats.SourceUseCache,'')
823
			WHERE TaxonFk = cdm_t.TaxonId 
824
			AND ISNULL(SourceUseCache,'') = ISNULL(bm_ats.SourceUseCache,'')
820 825
			AND ISNULL(SourceNameCache,'') = ISNULL(bm_ats.SourceNameCache,'')
821 826
			AND ISNULL(SourceDetail,'') = ISNULL(bm_ats.SourceDetail,'')
822 827
			)
......
833 838
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
834 839
		WHERE NOT EXISTS
835 840
		(SELECT * FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource
836
		WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(SourceUseCache,'') = ISNULL(bm_ats.SourceUseCache,'')
841
		WHERE TaxonFk = cdm_t.TaxonId 
842
		AND ISNULL(SourceUseCache,'') = ISNULL(bm_ats.SourceUseCache,'')
837 843
		AND ISNULL(SourceNameCache,'') = ISNULL(bm_ats.SourceNameCache,'')
838 844
		AND ISNULL(SourceDetail,'') = ISNULL(bm_ats.SourceDetail,'')
839 845
		)
846
		ORDER BY bm_t.Fullname, bm_ats.SourceUseCache, bm_ats.SourceNameCache
847
		
840 848
	*/
841 849
		SELECT @n_cdm = COUNT(*) -- additional sources only in CDM_EM2PESI
842 850
			FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource cdm_ats INNER JOIN
......
845 853
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
846 854
			WHERE NOT EXISTS
847 855
			(SELECT * FROM [EM2PESI].[DBO].AdditionalTaxonSource
848
			WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(SourceUseCache,'') = ISNULL(cdm_ats.SourceUseCache,'')
856
			WHERE TaxonFk = bm_t.TaxonId 
857
			AND ISNULL(SourceUseCache,'') = ISNULL(cdm_ats.SourceUseCache,'')
849 858
			AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ats.SourceNameCache,'')
850 859
			AND ISNULL(SourceDetail,'') = ISNULL(cdm_ats.SourceDetail,'')
851 860
			)
......
862 871
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
863 872
		WHERE NOT EXISTS
864 873
		(SELECT * FROM [EM2PESI].[DBO].AdditionalTaxonSource
865
		WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(SourceUseCache,'') = ISNULL(cdm_ats.SourceUseCache,'')
874
		WHERE TaxonFk = bm_t.TaxonId 
875
		AND ISNULL(SourceUseCache,'') = ISNULL(cdm_ats.SourceUseCache,'')
866 876
		AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ats.SourceNameCache,'')
867 877
		AND ISNULL(SourceDetail,'') = ISNULL(cdm_ats.SourceDetail,'')
868 878
		)
879
		ORDER BY bm_t.Fullname, cdm_ats.SourceUseCache, cdm_ats.SourceNameCache
869 880
	*/
870 881
		IF @n_cdm = 0 AND @n_bm = 0 BEGIN
871 882
			PRINT ('All additional sources are identical in both databases')
......
970 981
			WHERE NOT EXISTS
971 982
			(SELECT * FROM [CDM_EM2PESI].[DBO].Note LEFT OUTER JOIN [CDM_EM2PESI].[DBO].NoteSource
972 983
			ON [CDM_EM2PESI].[DBO].Note.NoteId = [CDM_EM2PESI].[DBO].NoteSource.NoteFk
973
			WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'')
984
			WHERE TaxonFk = cdm_t.TaxonId 
985
			AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'')
974 986
			AND ISNULL(Note_2,'') = ISNULL(bm_n.Note_2,'')
975 987
			AND ISNULL(NoteCategoryCache,'') = ISNULL(bm_n.NoteCategoryCache,'')
976 988
			AND ISNULL(LanguageCache,'') = ISNULL(bm_n.LanguageCache,'')
977 989
			AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_n.SpeciesExpertName,'')
978 990
			AND ISNULL(LastAction,'') = ISNULL(bm_n.LastAction,'')
979
			AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_n.LastActionDate,'00:00:00')
991
			AND Left(ISNULL(LastActionDate,'00:00:00'),18) = Left(ISNULL(bm_n.LastActionDate,'00:00:00'),18)
980 992
			AND ISNULL(SourceNameCache,'') = ISNULL(bm_ns.SourceNameCache,'')
981 993
			AND ISNULL(SourceDetail,'') = ISNULL(bm_ns.SourceDetail,'')
982 994
			)
......
985 997
			PRINT ('All note * notesource results in EM2PESI exist also in CDM_EM2PESI')
986 998
		END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing note * notesource results for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
987 999
	/*
1000
		Typical Problems: 
1001
		Duplicate entries during E+M SQL import for taxa using the same name object.
1002
		Missing source info by E+M SQL import
1003
		Last Action date automatically created (but wrong) by CDM import
1004
		
988 1005
		SELECT bm_t.Fullname, bm_n.Note_1, bm_n.Note_2, bm_n.NoteCategoryCache, bm_n.LanguageCache,
989 1006
		bm_n.SpeciesExpertName, bm_n.LastAction, bm_n.LastActionDate, bm_ns.SourceNameCache, bm_ns.SourceDetail,
990 1007
		'in EM2PESI but not in CDM_EM2PESI'
......
996 1013
		WHERE NOT EXISTS
997 1014
		(SELECT * FROM [CDM_EM2PESI].[DBO].Note LEFT OUTER JOIN [CDM_EM2PESI].[DBO].NoteSource
998 1015
		ON [CDM_EM2PESI].[DBO].Note.NoteId = [CDM_EM2PESI].[DBO].NoteSource.NoteFk
999
		WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'')
1016
		WHERE TaxonFk = cdm_t.TaxonId 
1017
		AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'')
1000 1018
		AND ISNULL(Note_2,'') = ISNULL(bm_n.Note_2,'')
1001 1019
		AND ISNULL(NoteCategoryCache,'') = ISNULL(bm_n.NoteCategoryCache,'')
1002 1020
		AND ISNULL(LanguageCache,'') = ISNULL(bm_n.LanguageCache,'')
1003 1021
		AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_n.SpeciesExpertName,'')
1004 1022
		AND ISNULL(LastAction,'') = ISNULL(bm_n.LastAction,'')
1005
		AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_n.LastActionDate,'00:00:00')
1023
		AND Left(ISNULL(LastActionDate,'00:00:00'),18) = Left(ISNULL(bm_n.LastActionDate,'00:00:00'),18)
1006 1024
		AND ISNULL(SourceNameCache,'') = ISNULL(bm_ns.SourceNameCache,'')
1007 1025
		AND ISNULL(SourceDetail,'') = ISNULL(bm_ns.SourceDetail,'')
1008 1026
		)
1027
		ORDER BY bm_n.NoteCategoryFk, bm_t.Fullname, bm_n.Note_1
1009 1028
	*/
1010 1029
		SELECT @n_cdm = COUNT(*) -- note * notesource results only in CDM_EM2PESI
1011 1030
			FROM [CDM_EM2PESI].[DBO].Note cdm_n INNER JOIN
......
1016 1035
			WHERE NOT EXISTS
1017 1036
			(SELECT * FROM [EM2PESI].[DBO].Note LEFT OUTER JOIN [EM2PESI].[DBO].NoteSource
1018 1037
			ON [EM2PESI].[DBO].Note.NoteId = [EM2PESI].[DBO].NoteSource.NoteFk
1019
			WHERE TaxonFk = bm_t.TaxonId AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'')
1038
			WHERE TaxonFk = bm_t.TaxonId 
1039
			AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'')
1020 1040
			AND ISNULL(Note_2,'') = ISNULL(cdm_n.Note_2,'')
1021 1041
			AND ISNULL(NoteCategoryCache,'') = ISNULL(cdm_n.NoteCategoryCache,'')
1022 1042
			AND ISNULL(LanguageCache,'') = ISNULL(cdm_n.LanguageCache,'')
1023 1043
			AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_n.SpeciesExpertName,'')
1024 1044
			AND ISNULL(LastAction,'') = ISNULL(cdm_n.LastAction,'')
1025
			AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_n.LastActionDate,'00:00:00')
1045
			AND LEFT(ISNULL(LastActionDate,'00:00:00'),18) = LEFT(ISNULL(cdm_n.LastActionDate,'00:00:00'),18)
1026 1046
			AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ns.SourceNameCache,'')
1027 1047
			AND ISNULL(SourceDetail,'') = ISNULL(cdm_ns.SourceDetail,'')
1028 1048
			)
......
1042 1062
		WHERE NOT EXISTS
1043 1063
		(SELECT * FROM [EM2PESI].[DBO].Note LEFT OUTER JOIN [EM2PESI].[DBO].NoteSource
1044 1064
		ON [EM2PESI].[DBO].Note.NoteId = [EM2PESI].[DBO].NoteSource.NoteFk
1045
		WHERE TaxonFk = bm_t.TaxonId AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'')
1065
		WHERE TaxonFk = bm_t.TaxonId 
1066
		AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'')
1046 1067
		AND ISNULL(Note_2,'') = ISNULL(cdm_n.Note_2,'')
1047 1068
		AND ISNULL(NoteCategoryCache,'') = ISNULL(cdm_n.NoteCategoryCache,'')
1048 1069
		AND ISNULL(LanguageCache,'') = ISNULL(cdm_n.LanguageCache,'')
1049 1070
		AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_n.SpeciesExpertName,'')
1050 1071
		AND ISNULL(LastAction,'') = ISNULL(cdm_n.LastAction,'')
1051
		AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_n.LastActionDate,'00:00:00')
1072
		AND LEFT(ISNULL(LastActionDate,'00:00:00'),18) = LEFT(ISNULL(cdm_n.LastActionDate,'00:00:00'),18)
1052 1073
		AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ns.SourceNameCache,'')
1053 1074
		AND ISNULL(SourceDetail,'') = ISNULL(cdm_ns.SourceDetail,'')
1054 1075
		)
1076
		ORDER BY cdm_n.NoteCategoryFk, cdm_t.Fullname,cdm_n.Note_1
1077
		
1055 1078
	*/
1056 1079
		IF @n_cdm = 0 AND @n_bm = 0 BEGIN
1057 1080
			PRINT ('All note * notesource results are identical in both databases')
......
1111 1134
		WHERE NOT EXISTS
1112 1135
		(SELECT * FROM [CDM_EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [CDM_EM2PESI].[DBO].OccurrenceSource
1113 1136
		ON [CDM_EM2PESI].[DBO].Occurrence.OccurrenceId = [CDM_EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
1114
		WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(TaxonFullNameCache,'') = ISNULL(bm_o.TaxonFullNameCache,'')
1137
		WHERE TaxonFk = cdm_t.TaxonId 
1138
		AND ISNULL(TaxonFullNameCache,'') = ISNULL(bm_o.TaxonFullNameCache,'')
1115 1139
		AND ISNULL(AreaNameCache,'') = ISNULL(bm_o.AreaNameCache,'')
1116 1140
		AND ISNULL(OccurrenceStatusCache,'') = ISNULL(bm_o.OccurrenceStatusCache,'')
1117 1141
		AND ISNULL(Notes,'') = ISNULL(bm_o.Notes,'')
......
1121 1145
		AND ISNULL(SourceNameCache,'') = ISNULL(bm_os.SourceNameCache,'')
1122 1146
		AND ISNULL(OldTaxonName,'') = ISNULL(bm_os.OldTaxonName,'')
1123 1147
		)
1148
		ORDER BY bm_t.Fullname, AreaNameCache 
1124 1149
	*/
1125 1150
		SELECT @n_cdm = COUNT(*) -- occurrence * occurrencesource results only in CDM_EM2PESI
1126 1151
			FROM [CDM_EM2PESI].[DBO].Occurrence cdm_o INNER JOIN
......
1147 1172
			PRINT ('All occurrence * occurrencesource results in CDM_EM2PESI exist also in EM2PESI')
1148 1173
		END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing occurrence * occurrencesource results for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
1149 1174
	/*
1150
		SELECT cdm_t.TaxonId as cdmTID, bm_t.TaxonId bmTID, bm_t.Fullname, cdm_o.TaxonFullNameCache, cdm_o.AreaNameCache, cdm_o.OccurrenceStatusCache, cdm_o.Notes,
1175
		SELECT cdm_t.TaxonId as cdmTID, bm_t.TaxonId bmTID, cdm_t.Fullname, cdm_o.TaxonFullNameCache, cdm_o.AreaNameCache, cdm_o.OccurrenceStatusCache, cdm_o.Notes,
1151 1176
		cdm_o.SpeciesExpertName, cdm_o.LastAction, cdm_o.LastActionDate, cdm_os.SourceNameCache, cdm_os.OldTaxonName,
1152 1177
		'in CDM_EM2PESI but not in EM2PESI'
1153 1178
		FROM [CDM_EM2PESI].[DBO].Occurrence cdm_o INNER JOIN
......
1158 1183
		WHERE NOT EXISTS
1159 1184
		(SELECT * FROM [EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [EM2PESI].[DBO].OccurrenceSource
1160 1185
		ON [EM2PESI].[DBO].Occurrence.OccurrenceId = [EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
1161
		WHERE TaxonFk = bm_t.TaxonId AND ISNULL(TaxonFullNameCache,'') = ISNULL(cdm_o.TaxonFullNameCache,'')
1186
		WHERE TaxonFk = bm_t.TaxonId 
1187
		AND ISNULL(TaxonFullNameCache,'') = ISNULL(cdm_o.TaxonFullNameCache,'')
1162 1188
		AND ISNULL(AreaNameCache,'') = ISNULL(cdm_o.AreaNameCache,'')
1163 1189
		AND ISNULL(OccurrenceStatusCache,'') = ISNULL(cdm_o.OccurrenceStatusCache,'')
1164 1190
		AND ISNULL(Notes,'') = ISNULL(cdm_o.Notes,'')
......
1168 1194
		AND ISNULL(SourceNameCache,'') = ISNULL(cdm_os.SourceNameCache,'')
1169 1195
		AND ISNULL(OldTaxonName,'') = ISNULL(cdm_os.OldTaxonName,'')
1170 1196
		)
1197
		ORDER BY bm_t.Fullname, AreaNameCache 
1171 1198
	*/
1172 1199
		IF @n_cdm = 0 AND @n_bm = 0 BEGIN
1173 1200
			PRINT ('All occurrence * occurrencesource results are identical in both databases')

Also available in: Unified diff

Add picture from clipboard (Maximum size: 40 MB)