ProSolution Student Insert Script Template

A template insert script for syncing ProSolution student data into Navigate. It takes data from the NavigateStudentsExport view and inserts new students that are not already in the Navigate database.

What the script does

This is a template insert script ([ProSolutionReports].[dbo].[Navigate_StudentInsert]) which takes data from the NavigateStudentsExport view. The script checks if a Student Details ID is currently in Navigate's database:

• If it finds the ID, it ignores that student.
• If it does not, it inserts the new student.

Version: 1.0  |  Created by: Navigate  |  Created date: 22/03/25 (Initial Development).

Before you run it

• Replace [Insert your specific college schema here] with your specific college schema name.
• Change the source view name [ProSolutionReports].[dbo].[vNavigateStudentsExport] if needed for your environment.
• DateAdded must not be null.
• DateUpdated and DateProcessed are for Navigate use — do not add data to these fields.

Template insert script

INSERT INTO [NAVIGATE].[Navigate_ExternalFeeds].[Insert your specific college schema here].[tblUsers] (
	[StudentNumber]
     ,[StudentFirstName]
     ,[StudentSurname]
     ,[StudentEmail]
     ,[StudentUsername]
     ,[TutorFirstName]
     ,[TutorSurname]
     ,[TutorEmail]
     ,[Attribute1]
     ,[Attribute1Code]
     ,[Attribute2]
     ,[Attribute2Code]
     ,[Attribute3]
     ,[Attribute3Code]
     ,[Attribute4]
     ,[Attribute4Code]
     ,[Attribute5]
     ,[Attribute5Code]
     ,[Attribute6]
     ,[Attribute6Code]
     ,[ExternalID]
     ,[DateAdded]
	 ,[DateUpdated]
	 ,[DateProcessed]
     ,[Active]
	 ,[EHCPStatus]
)
SELECT
	[StudentNumber]
	,[StudentFirstName]
	,[StudentSurname]
	,[StudentEmail]
	,[StudentUsername]
	,[TutorFirstName]
	,[TutorSurname]
	,[TutorEmail]
	,[Attribute1]
	,[Attribute1Code]
	,[Attribute2]
	,[Attribute2Code]
	,[Attribute3]
	,[Attribute3Code]
	,[Attribute4]
	,[Attribute4Code]
	,[Attribute5]
	,[Attribute5Code]
	,[Attribute6]
	,[Attribute6Code]
	,[ExternalID]
	,[DateAdded]
	,[DateUpdated]
	,[DateProcessed]
	,[Active]
	,[EHCPStatus]
FROM
(
SELECT
		vSE.[StudentNumber] As [StudentNumber]
      ,vSE.[StudentFirstName] As [StudentFirstName]
      ,vSE.[StudentSurname]   As [StudentSurname]
      ,vSE.[StudentEmail]     As [StudentEmail]
      ,vSE.[StudentUsername]  As [StudentUsername]
      ,vSE.[TutorFirstName]   As [TutorFirstName]
      ,vSE.[TutorSurname]     As [TutorSurname]
      ,vSE.[TutorEmail]       As [TutorEmail]
      ,vSE.[Attribute1]       As [Attribute1]
      ,vSE.[Attribute1Code]   As [Attribute1Code]
      ,vSE.[Attribute2]       As [Attribute2]
      ,vSE.[Attribute2Code]   As [Attribute2Code]
      ,vSE.[Attribute3]       As [Attribute3]
      ,vSE.[Attribute3Code]   As [Attribute3Code]
      ,vSE.[Attribute4]       As [Attribute4]
      ,vSE.[Attribute4Code]   As [Attribute4Code]
      ,vSE.[Attribute5]       As [Attribute5]
      ,vSE.[Attribute5Code]   As [Attribute5Code]
      ,vSE.[Attribute6]       As [Attribute6]
      ,vSE.[Attribute6Code]   As [Attribute6Code]
      ,vSE.[StudentNumber]    As [ExternalID]
      ,GETDATE()              As [DateAdded] -- Must not be null.
	  ,NULL                   As [DateUpdated] -- Navigate use, do not add data here.
	  ,NULL                   As [DateProcessed] -- Navigate use, do not add data here.
      ,vSE.[Active]           As [Active]
	  ,vSE.[EHCPStatus]       As [EHCPStatus]
FROM [ProSolutionReports].[dbo].[vNavigateStudentsExport] vSE --Change name if needed
LEFT JOIN [NAVIGATE].[Navigate_ExternalFeeds].[Insert your specific college schema here].[tblUsers] as Nav
on vSE.[ExternalID] = Nav.[ExternalID]
WHERE Nav.[ExternalID] IS NULL --Where not in Navigate database.

Source document attached below.