开发者

Creating an insert query (error on foreign key constraint)

I want to move data from one database's table to another database's table. I am getting a foreign key error. How can I insert all the data which is valid except those rows who don't have a foreign key?

My query is :

  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 state开发者_如何学Pythonment conflicted with the FOREIGN KEY constraint "FK__city__countryid__3E52440B". The conflict occurred in database "schoolHigher", table "dbo.country", column 'countryId'.


INNER JOIN the other database's table with the country table. Only those records with an existing country will get selected.

Note: you should check that the corresponding countryid's in both databases match.

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜