I have a question If I can populate data to table by executing View in sql server agent using weekly job or is it only possible using stored procedure?
View1:
ALTER VIEW [dbo].[Destinations_1415]
AS
select
sy.styr_student_id,'2014/15' 'Year',q.Surname
,q.Forename,q.Course,pit.prpi_title
,CASE WHEN q.Section = 'BATT' THEN 'BATS' ELSE q.Section collate Latin1_General_CI_AS END AS'Section'
,q.Funding_Type,q.Funding_Body,q.Partner
,c.GNIC_Description 'Outcome',c2.GNIC_Description 'Outcome_Specific'
,q.Completion_Status,sd.stud_mobile_telephone,sd.stud_home_telephone_no
,CASE WHEN sy.STYR_Age_end_Aug < 16 THEN '1416' WHEN sy.STYR_Age_end_Aug < 19 THEN '1618' ELSE '19+' END AgeBand
from sql10.ng.dbo.styrstudentyr sy
Inner join(
SELECT [Partner Name] 'Partner',[Funding Body] 'Funding_Body'
,[Funding Line] 'Funding_Type', [LearnRefNumber] 'learnerrefnumber'
,ROW_NUMBER()OVER(Partition By LearnRefNumber, [funding line] Order By CompStatus) 'row_no'
,[FamilyName] 'Surname',[GivenNames] 'Forename',[Full Name]
,[ProvSpecDelMon_A] 'Course',[ProvSpecDelMon_B] 'Section',[LearnAimRef] 'Learning_Aim'
,[LearnAimRefTitle] 'Learning_Aim_Title',[FundModel] 'Fundmodel'
,[CompStatus] 'Completion_Status'
FROM [CollegeReporting].[dbo].[PFR_1415_tbl]
where [Funding Line] not like '%appre%'
union all
SELECT [Partner Name] 'Partner',[Funding Body] 'Funding_Body'
,[Funding Line] 'Funding_Type',[LearnRefNumber] 'learnerrefnumber'
,ROW_NUMBER()OVER(Partition By LearnRefNumber, [funding line] Order By CompStatus) 'row_no'
,[FamilyName] 'Surname',[GivenNames] 'Forename',[Full Name]
,[ProvSpecDelMon_A] 'Course',[ProvSpecDelMon_B] 'Section',[LearnAimRef] 'Learning_Aim'
,[LearnAimRefTitle] 'Learning_Aim_Title',[FundModel] 'Fundmodel',[CompStatus] 'Completion_Status'
FROM [CollegeReporting].[dbo].[PFR_1415_tbl]
where [Funding Line] like '%appre%'
and LearnAimRef = 'ZPROG001')q on q.learnerrefnumber collate SQL_Latin1_General_CP1_CI_AS = sy.styr_student_id collate SQL_Latin1_General_CP1_CI_AS and q.row_no = '1'
left join sql10.NG.dbo.STDPOutcome o on o.STDP_Student_ID = sy.styr_student_id and o.STDP_Course_Text = '2014'
left join sql10.ng.dbo.GNICodes c on c.GNIC_Code = STDP_Outcome_Type and c.GNIC_Type = 'OUT'
left join sql10.ng.dbo.GNICodes c2 on c2.GNIC_Code = STDP_Outcome_Code and STDP_Outcome_Type = c2.GNIC_Type
left join sql10.ng.dbo.studstudent sd on sd.stud_student_id = sy.styr_student_id
inner join sql10.ng.dbo.PRPIProvisionInstance pit on pit.prpi_code collate Latin1_General_CI_AS = q.Course collate Latin1_General_CI_AS and pit.prpi_instance = '141501'
where sy.styr_year = '2014'
View 2:
ALTER VIEW [dbo].[Destinations_1516]
AS
select
sy.styr_student_id,'2015/16' as 'Year'
,q.Surname,q.Forename,q.Course
,pit.prpi_title,q.Section collate Latin1_General_CI_AS 'Section',q.Funding_Type
,q.Funding_Body,q.Partner,c.GNIC_Description 'Outcome'
,c2.GNIC_Description 'Outcome_Specific',q.Completion_Status
,sd.stud_mobile_telephone,sd.stud_home_telephone_no
, CASE WHEN sy.STYR_Age_end_Aug < 16 THEN '1416' WHEN sy.STYR_Age_end_Aug < 19 THEN '1618' ELSE '19+' END AgeBand
from sql10.ng.dbo.styrstudentyr sy
Inner join(
SELECT Partner 'Partner'
,[Funding Body] 'Funding_Body',[Funding Line] 'Funding_Type'
,[LearnRefNumber] 'learnerrefnumber'
,ROW_NUMBER()OVER(Partition By LearnRefNumber, [funding line] Order By CompStatus) 'row_no'
,[FamilyName] 'Surname',[GivenNames] 'Forename'
,[Full Name],[coursecode] 'Course'
,[section] 'Section',[LearnAimRef] 'Learning_Aim'
,[coursetitle] 'Learning_Aim_Title',[Fund Model] 'Fundmodel'
,[CompStatus] 'Completion_Status'
FROM [CollegeReporting].[dbo].[PFR_1516]
where [Funding Line] not like '%appre%'
union all
SELECT Partner 'Partner',[Funding Body] 'Funding_Body',[Funding Line] 'Funding_Type'
,[LearnRefNumber] 'learnerrefnumber'
,ROW_NUMBER()OVER(Partition By LearnRefNumber, [funding line] Order By CompStatus) 'row_no'
,[FamilyName] 'Surname',[GivenNames] 'Forename'
,[Full Name],[coursecode] 'Course'
,[section] 'Section',[LearnAimRef] 'Learning_Aim'
,[coursetitle] 'Learning_Aim_Title',[Fund Model] 'Fundmodel'
,[CompStatus] 'Completion_Status'
FROM [CollegeReporting].[dbo].[PFR_1516]
where [Funding Line] like '%appre%'
and LearnAimRef = 'ZPROG001')q on q.learnerrefnumber collate SQL_Latin1_General_CP1_CI_AS = sy.styr_student_id collate SQL_Latin1_General_CP1_CI_AS and q.row_no = '1'
left join sql10.NG.dbo.STDPOutcome o on o.STDP_Student_ID = sy.styr_student_id and o.STDP_Course_Text = '2015'
left join sql10.ng.dbo.GNICodes c on c.GNIC_Code = STDP_Outcome_Type and c.GNIC_Type = 'OUT'
left join sql10.ng.dbo.GNICodes c2 on c2.GNIC_Code = STDP_Outcome_Code and STDP_Outcome_Type = c2.GNIC_Type
left join sql10.ng.dbo.studstudent sd on sd.stud_student_id = sy.styr_student_id
inner join sql10.ng.dbo.PRPIProvisionInstance pit on pit.prpi_code collate Latin1_General_CI_AS = q.Course collate Latin1_General_CI_AS and pit.prpi_instance = '151601'
where sy.styr_year = '2015'
View 3 (Union of View 1 and View2 ):
ALTER VIEW [dbo].[Destinations_1415_1516_Union]
AS
SELECT * FROM [dbo].[Destinations_1415]
UNION
SELECT * FROM [dbo].[Destinations_1516]
Error:
The OLE DB provider "SQLNCLI10" for linked server "sql10" reported a change in schema version between compile time ("182244063332028") and run time ("182274129276679") for table ""ng"."dbo"."styrstudentyr"".
Please let me know.