Protections needed against missing data.

[sqlHippo]

name.basics.tsv contains rows with NULL primaryName, which causes an error in Load IMDB relational tables.sql.

--- Principals
---------------------------------------

INSERT INTO dbo.Principals WITH (TABLOCKX, HOLDLOCK) (principalId, primaryName, birthYear, deathYear)
SELECT CAST(SUBSTRING(nameId, 3, 100) AS int) AS principalId, primaryName,
       DATEFROMPARTS(birthYear, 1, 1), DATEFROMPARTS(deathYear, 12, 31)
FROM [Raw].[name.basics.tsv.gz];

Msg 515, Level 16, State 2, Line 5
Cannot insert the value NULL into column 'primaryName', table 'IMDB.dbo.Principals'; column does not allow nulls. INSERT fails.
The statement has been terminated.

[sqlHippo]

This also appears to have some down stream impacts on subsequent table loads.

Msg 515, Level 16, State 2, Line 5
Cannot insert the value NULL into column 'primaryName', table 'IMDB.dbo.Principals'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(47 rows affected)
Msg 547, Level 16, State 0, Line 27
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PrimaryProfession_Principal". The conflict occurred in database "IMDB", table "dbo.Principals", column 'principalId'.
The statement has been terminated.

(28 rows affected)

(11 rows affected)

(11243028 rows affected)

(1 row affected)

(0 rows affected)

(17405682 rows affected)
Warning: Null value is eliminated by an aggregate or other SET operation.

(50547455 rows affected)

(163 rows affected)

(291249 rows affected)

(6 rows affected)

(276651 rows affected)

(0 rows affected)

(1790286 rows affected)

(16009999 rows affected)

(2006971 rows affected)

(8100586 rows affected)

(19223158 rows affected)

(170310 rows affected)

(893372 rows affected)
Msg 547, Level 16, State 0, Line 231
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TitlePrincipals_Profession". The conflict occurred in database "IMDB", table "dbo.Professions", column 'professionId'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 250
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TitleCharacters_Episode". The conflict occurred in database "IMDB", table "dbo.Titles", column 'titleId'.
The statement has been terminated.

(1499983 rows affected)

Completion time: 2024-11-16T18:13:09.4315071-06:00