Aron's Tech Blog

So how many time did you think to yourself, wouldn’t it be nice to have a repository of names an addresses so I can load for testing? If you’re normal you’ve never asked yourself that. If you’re a computer geek it crosses your mind from time to time. Microsoft has generously given us the Adventure Works database and this is perfect for just such repository. You can find the DB on Microsoft’s site and here is a bit of SQL to get you started.

SELECT top 1000
	   replace(pe.EmailAddress, '@adventure-works.com', '@invalidemail.com') as Ident
	  --,p2.rowguid
      --,isnull(p2.[Title], '') as Title
      ,isnull(p2.[FirstName], '') as FirstName
      ,isnull(p2.[MiddleName], '') as MiddleName
      ,isnull(p2.[LastName], '') as LastName
      --,isnull(p2.[Suffix], '') as Suffix
	  ,replace(pa.AddressLine1, ',', '-') as AddressLine1
	  ,isnull(pa.AddressLine2, '') as AddressLine2
	  ,pa.City
	  --,sp.StateProvinceCode as State
	  --,sp.CountryRegionCode as Country
	  ,pa.PostalCode
	  ,replace(pe.EmailAddress, '@adventure-works.com', '@invalidemail.com') as EmailAddress
	  ,isnull((select pp.PhoneNumber where pp.PhoneNumberTypeID = 1), '') as CellPhone
	  ,isnull((select pp.PhoneNumber where pp.PhoneNumberTypeID = 2), '') as HomePhone
	  ,isnull((select pp.PhoneNumber where pp.PhoneNumberTypeID = 3), '') as WorkPhone
	  ,Lower(LEFT(p2.FirstName, 1) + p2.LastName) as Username
	  ,'taleo123' as Pswd
  FROM [Person].[Person] p2
  join Person.BusinessEntityAddress ba on (p2.BusinessEntityID = ba.BusinessEntityID)
  join Person.Address pa on (ba.AddressID = pa.AddressID)
  join Person.StateProvince sp on (sp.StateProvinceID = pa.StateProvinceID)
  join Person.EmailAddress pe on (p2.BusinessEntityID = pe.BusinessEntityID)
  join Person.PersonPhone pp on (p2.BusinessEntityID = pp.BusinessEntityID)
  where sp.CountryRegionCode = 'US'
order by LastName, FirstName