SQL Select Query Error

See more:

SQL-Server-2005

SQL-Server

The bscode column is FK to emrcode of m_emrcodes table

The select query is also trying to insert NULLs to the bscode column thus returning integrity error. The cause of the error is known but need to modify the below select query to rows having bscode as NULL

Any suggestions!!!

insert into RegClient.DBClient.dbo.m_bscomplaint(bscode,bscomplaintname,normal,responsetype,sortorder,active)
select (select top 1 e_emr.emrcode from RegClient.DBClient.dbo.m_emrcodes e_emr
        inner join RegClient.DBClient.dbo.emrcodes_map map on e_emr.emrcode = map.RegClient_emrcode
        inner join RegServer.DBServer.dbo.m_emrcodes emr on emr.emrcode = map.RegServer_emrcode
        inner join RegServer.DBServer.dbo.m_bscomplaint inst on emr.emrcode = inst.bscode
        Where emr.emrtype in('H','R')
        and e_emr.emrcode = map.RegServer_emrcode
        and map.RegClient_emrcode = e_emr.emrcode
        and e_emr.emrcode in(1041,1060,1150,975,1044,1047,1046,1118,1114,1179,1011,1061,994,1147,1090)
        ) as bscode,temp.bscomplaintname,temp.normal,temp.responsetype,temp.sortorder,temp.active
from RegServer.DBServer.dbo.m_bscomplaint temp