Create insert request (foreign key constraint error)

Question

I want to move data from one database table to another database table. I get a foreign key error. How can I insert all valid data except rows that do not have a foreign key?

My request:

  SET IDENTITY_INSERT City ON

  INSERT INTO City ([cityid],[city],[country],[state],[cityinfo]
  ,[enabled],[countryid],[citycode],[stateid],[latitude],[longitude])
  SELECT [cityid],[city],[country],[state],[cityinfo]
  ,[enabled],[countryid],[citycode],[stateid],[latitude],[longitude]
  FROM TD.DBo.City

getting this error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__city__countryid__3E52440B". The conflict occurred in database "schoolHigher", table "dbo.country", column 'countryId'.

Answer 1

INNER JOINanother database table with a table country. Only those records with an existing country will be selected.

Note: you must check the relevance of the respective country in both databases.

SET IDENTITY_INSERT City ON

INSERT INTO City (
    [cityid]
    ,[city]
    ,[country]
    ,[state]
    ,[cityinfo]
    ,[enabled]
    ,[countryid]
    ,[citycode]
    ,[stateid]
    ,[latitude]
    ,[longitude])
SELECT  ct.[cityid]
        ,ct.[city]
        ,ct.[country]
        ,ct.[state]
        ,ct.[cityinfo]
        ,ct.[enabled]
        ,ct.[countryid]
        ,ct.[citycode]
        ,ct.[stateid]
        ,ct.[latitude]
        ,ct.[longitude]
FROM  TD.DBo.City ct
      INNER JOIN dbo.Country cnt ON cnt.CountryID = ct.CountryID