luSchool lookup for ACARA

Modified on Tue, 24 Feb at 4:55 AM

This is only valid if your luSchool lookup table is using the ACARA ID as the code.

If this is not the case, please speak to the Synergetic consulting team for further information.

Download the Excel version of the list with the required parameters (ie. State) from https://asl.acara.edu.au/School-Search

Prefix the name with a 'u' and save this as Text (Tab delimited)

Use the Data Import Tool to import this into your database using the following configuration as an example.

Once the data has successfully imported, you can make the necessary updates or additions as required.

These are example scripts only.
These should only be run if you are 100% comfortable that these are relevant for your particular environment.

-- UPDATE OPEN/CLOSED SCHOOLS
-- ==========================
SELECT sr.* 
-- UPDATE dbo.luSchool SET ActiveFlag = CASE WHEN sr.Status = 'Open' Then 1 ELSE 0 END
FROM dbo.uSearchResults sr
INNER JOIN dbo.luSchool sc
  ON sr.[ACARA ID] = sc.Code
WHERE sc.ActiveFlag <> CASE WHEN sr.Status = 'Open' Then 1 ELSE 0 END

-- INSERT ADDITIONAL SCHOOLS
-- ========================
SELECT  
-- INSERT dbo.luSchools (Code, Description, Suburb, State, PostCode, ActiveFlag) SELECT 
  sr.[ACARA ID],
  sr.[School Name],
  sr.Suburb,
  sr.State,
  sr.PostCode,
  ActiveFlag = CASE WHEN sr.Status = 'Open' Then 1 ELSE 0 END
FROM dbo.uSearchResults sr
WHERE sr.[ACARA ID] NOT IN (SELECT Code from dbo.luSchool)

-- DROP THE TEMP USER TABLE
-- ========================
DROP TABLE dbo.uSearchResults