CODEWITHSUNDEEP
sqlsql-server-2008
legitcoding
legitcoding

Reputation: 21

I cant insert into sql

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

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

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

Related Questions