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
精彩评论