跨库查询

--select * from tblstudentdata where student_id='19840101'
--delete from tblstudentdata where student_id='0220129080'
--
--select * from tblstudentdata where student_id='0220129080'  --0220020454
--
--declare @count as integer;set @count=0;select @count=count(1) from tblstudentdata where student_id='0220129080';if(@count=1)begin;
--select 1
--end




--select housedescription,PupilID,StudentNo as current_house from _v_M1_Card  where  PupilID = 1563 --1563
if object_id('tempdb..#tmpEngageData') is not null
drop table #tmpEngageData
if object_id('tempdb..#tmp2') is not null
drop table #tmp2
if object_id('tempdb..#tmp3') is not null
drop table #tmp3
select housedescription COLLATE Latin1_General_CI_AI as current_house,PupilID,StudentNo COLLATE Latin1_General_CI_AI as StudentNo into #tmpEngageData from
OPENDATASOURCE('SQLOLEDB','Data Source=192.168.2.2;User ID=sa;Password=000000#').EngageHarrowInternational.dbo.[_v_M1_Card]

select surname , current_house COLLATE Latin1_General_CI_AI as house1,student_id COLLATE Latin1_General_CI_AI as student_id into #tmp2 from tblstudentdata
select a.*,b.* into #tmp3 from #tmpEngageData a left join #tmp2 b on a.studentNo=b.student_id
select * from #tmp3 where house1<>current_house --and house1 =''
--select top 10 student_id,surname,current_house from tblstudentdata --where student_id='19840101'