Reputation: 21
IMDB database Image to database structure FletNix
INSERT INTO FletNix_2.dbo.Movie_Cast
SELECT cast(Pid AS int) AS person_id,
cast(Mid AS int) AS movie_id,
LEFT(Role, 255) AS role
FROM MYIMDB.dbo.Imported_Cast
INSERT INTO FletNix_2.dbo.Movie_Directors
SELECT cast(Did AS int) AS person_id,
cast(Mid AS int) AS movie_id
FROM MYIMDB.dbo.Imported_Movie_Directors
ERROR MASSAGE:
Msg 2627, Level 14, State 1, Line 71 Violation of PRIMARY KEY constraint 'pk_moviecast'. Cannot insert duplicate key in object 'dbo.Movie_Cast'. The duplicate key value is ((Unknown)).
Msg 547, Level 16, State 0, Line 77 The INSERT statement conflicted with the FOREIGN KEY constraint "fk_directors_Person". The conflict occurred in database "FletNix_2", table "dbo.Person", column 'person_id'.
I know i need to use distinct, but when i do
INSERT INTO FletNix_2.dbo.Movie_Cast
SELECT cast(Pid AS int) AS person_id,
cast(Mid AS int) AS movie_id,
distinct LEFT(Role, 255) AS role
FROM MYIMDB.dbo.Imported_Cast
it gives an error: incorrect syntax near distinct
When i put distinct after SELECT i still get a error:
Violation of PRIMARY KEY constraint 'pk_moviecast'. Cannot insert duplicate key in object 'dbo.Movie_Cast'. The duplicate key value is ((Unknown))
AND
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_directors_Person". The conflict occurred in database "FletNix_2", table "dbo.Person", column 'person_id'.
Upvotes: 0
Views: 68
Reputation: 32031
distinct should be after select
INSERT INTO FletNix_2.dbo.Movie_Cast
SELECT distinct cast(Pid AS int) AS person_id,
cast(Mid AS int) AS movie_id,
LEFT(Role, 255) AS role
FROM MYIMDB.dbo.Imported_Cast
as you role column is primary so it has not to be duplicated so i used row_number() for generation unique number
select
cast(Pid AS int) AS person_id,
cast(Mid AS int) AS movie_id,
row_number()over( order by (select null)) as role
FROM MYIMDB.dbo.Imported_Cast
Upvotes: 1