sample transactions – filling the empty db

Posted by david on 22. October 2008 09:27

creating sample data is a pain in the arse …

then I need to actually fill the transactions table with my bogus data.  retrace steps:

• create a sample set of users, took a list of 300 names & emails from the AdventureWorks db
• create a sample set of accounts for those users. 
  • Each user owns their own set of accounts and are completely variable – in production. 
  • So, to get some test data in there, without typing 1200 unique items, I created 4 basic accounts – a cable account, a creditcard, an electric account, and a waste disposal account.  
  • I inserted those into the accounts table, prefacing the account names with the userid. Result: 1200 unique accounts, but consistent enough that I might notice problems
• Finally, create transactions for each account. 
  • again in Excel, built a list of 33 transactions, 4 to 8 transactions per each of the 4 account types noted above.  Covers a few months of activity, varying dates, varying balances. 

  • To get these transactions into the db, I needed to massage the import table a little, assigning a code to the transactions representing the types of accounts.   I built a foreign key of sorts in the Accounts table, using the optional Account Number field as a temporary bucket.  This was so that I could Join the Accounts table to the transactionSample table to feed the Transactions table insert.  There was probably a way to do this without the intermediate step, but I was at a loss.

   1: UPDATE [Accounts]

   2:        SET AccountNumber = 

   3:            CASE 

   4:                WHEN (AccountName LIKE '%_CableTelevision') THEN 1

   5:                WHEN (AccountName LIKE '%_CreditCard') THEN 2                

   6:                WHEN (AccountName LIKE '%_ElectricUtilities') THEN 3                

   7:                WHEN (AccountName LIKE '%_WasteDisposal') THEN 4

   8:            END    

•  

  • Now I’ve got the accounts table linked to the transactionsample table, i can run an insert such that for every account, a complete set of sample transactions are added to the transactions table, linked appropriately to the unique accounts and the userID that the account was attached to:

   1: INSERT INTO billz.Transactions

   2:         (

   3:             UserID

   4:            ,AccountID

   5:            ,Amount

   6:            ,Note

   7:            ,UserDate

   8:         )

   9:     SELECT 

  10:          Accounts.UserID

  11:         ,Accounts.AccountID

  12:         ,transactionSample.Amount

  13:         ,transactionSample.Note

  14:         ,transactionSample.UserDate

  15:     FROM

  16:         billz.Accounts JOIN dbo.transactionSample ON

  17:         Accounts.AccountNumber = transactionSample.AccountCode