Bulk User Export Query

If you need to create a large number of users at once it's possible to do this by using the Bulk User Import process.

There are more details in the above linked article, but essentially, users should be contained in CSV format with data contained within quotes and must contain the following parameters:

First Name, Last Name, Initial, Salutation Code, Language Code, Time Zone Code, Email Address, UserName, Password, Role Code

This alone is useful for first time setup, but if you want to move users from one environment to another, you can extract these details with the following query:

SELECT p.FirstName, p.LastName, p.MiddleInitial, p.SalutationCode, p.PreferredLanguageCode, p.LocalTimeZoneCode, c.emailAddress, i.EmailLeft, i.Password, s.RoleCode
FROM person p
INNER JOIN IpContact c ON p.IpPerson = c.IpID
INNER JOIN IpClass i ON p.IpPerson = i.IpID
INNER JOIN StaffMemberRole s ON p.IpPerson = s.IpEmployee

If you want to expired users to be excluded, you can add the following clause to the end of the query:

WHERE i.enddate > now()and s.enddate > now();

The above syntax is correct for PostgreSQL and MySQL. For HSQLDB replace 'now()' with 'today', for MS SQL Server replace 'now()' with 'getdate()'.

Is this article helpful?
0 0 0