Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

SQL Server:  T-SQL recipe to create a million sample people

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.
Published:
Updated:
(Warning:  Heavy T-SQL ahead, divided into bite-size parts with explanations.  The full code is in the last code block. )

The following Microsoft SQL Server T-SQL code will create a table with a million rows of sample people with addresses, phone number, date of birth, and other demographic columns.

I have used variations of this code for lots of reasons:
  • You’re asked to work a project that doesn’t have sample data, and creating something on the fly with 'LastName1, LastName2' just doesn't look very cool.
  • You’re asked to work a project that contains ACTUAL PERSONAL DATA {alarms should go off here}, and need to de-identify the personal stuff.  Even more important if you need to assign this work to a non-employee.
  • You have data, but want to test certain functionality with a larger sample size.

For example, here's a real-life conversation I had back in 2010 that I never ever want to have again:
  • Me:  Hey boss, you know that contractor I just hired two weeks ago?  He ditched us two days ago with no notice.
  • VP boss:  Okay.  Since he was working on PHI data, how do you know that he didn't leave with all of our confidential data?
  • Me, thinking I never want to say this again.:  Well, I don't.

So let’s get this show started.

First, let’s create three lookup tables that we’ll use to populate our table of people:  Names, City-State-Zip, and Street Addresses.
-- 1-Drop list tables (if they currently exist)
                      IF OBJECT_ID('SAMPLE_NAME') IS NOT NULL
                      	DROP TABLE SAMPLE_NAME
                      
                      IF OBJECT_ID('SAMPLE_CITY_STATE_ZIP') IS NOT NULL
                      	DROP TABLE SAMPLE_CITY_STATE_ZIP
                      
                      IF OBJECT_ID('SAMPLE_STREET') IS NOT NULL
                      	DROP TABLE SAMPLE_STREET
                      
                      -- 1-Create list tables
                      CREATE TABLE SAMPLE_NAME (
                      	id int IDENTITY(1,1) PRIMARY KEY,  
                      	first_name varchar(50), 
                      	last_name varchar(50))
                      
                      CREATE TABLE SAMPLE_CITY_STATE_ZIP (
                      	id int IDENTITY(1,1) PRIMARY KEY, 
                      	city varchar(50), 
                      	state_code char(2), 
                      	zip_code varchar(5))
                      
                      CREATE TABLE SAMPLE_STREET (
                      	id int IDENTITY(1,1) PRIMARY KEY, 
                      	addr1 varchar(100), 
                      	addr2 varchar(100))		

Open in new window

Now let’s populate the name.  My first attempt was to do this manually, but eventually I ran out of people with whom I went to high school, played ice hockey, live in my neighborhood, and I worked with.  That and after some seriously worn-out hands I went looking for an automated solution.  

There’s a very useful website http://www.generatedata.com that can generate a sample set of different columns.  The steps I used to create the below code are:

•      In the Country-Specific Data listbox choose your country.
•      In the Data Set column select three columns:  Names-Alex (any gender), Names-Smith (Surname).
•      In the Add ___ Rows textbox, choose 200.
•      In the Export Types, choose Database table name=’SAMPLE_NAME’, Database Name = SQL Server, Statement Type=Insert.
•      Hit the ‘Generate’ button.

Generate Sample Names, IHitting the Calculate button results in this T-SQL Insert statements..
Generate Sample Names, IIAfter copy-pasting the T-SQL into SSMS, and doing some cleanup, my code looks like this:
 
-- 1-Populate list tables	
                      
                      -- NAMES:  Generated using http://www.generatedata.com/#generator
                      INSERT INTO SAMPLE_NAME (first_name, last_name)
                      VALUES 
                      ('Janet', 'Smith'), ('Frank', 'Bloswick'), ('Tonya', 'Bazinaw'), ('Mike', 'St. Onge'),
                      ('Jackie', 'Jones'), ('Darren', 'Tillbrooke'),('Stephanie', 'Holsinger'), ('Rene', 'Hughey'),
                      ('Robert', 'Rogers'), ('Richard', 'LaPine'), ('Kathy', 'Summerfield'), ('Kathy', 'Bodwin'),
                      ('Mitch', 'Krause'), ('George', 'Dow'), ('Jack', 'Malone'), ('Bill', 'Schweiz'),
                      ('Mark', 'Gunter'), ('Bob', 'Anderson'),('Scott', 'Simpson'), ('Phil', 'Dingman'),
                      ('Chad', 'Leiker'), ('Ian', 'Benson'), ('Nicole', 'Lane'), ('Steve', 'Lundeen'),
                      ('Erica','Black'), ('Xenos','Mathis'), ('Kyle','Good'), ('Raja','Dejesus'), ('Timothy','Frazier'), 
                      ('Francine','Morrison'), ('Avram','Pate'), ('Hammett','Coffey'), ('Hasad','Wise'), ('Cullen','Riddle'), 
                      ('Kato','Delgado'), ('Todd','Wright'), ('Troy','Mccoy'), ('Gil','Duncan'), ('Lionel','Espinoza'), 
                      ('Victor','Merrill'), ('Gennifer','Vance'), ('Chancellor','Warner'), ('Davis','Wolf'), 
                      ('Carlos','Clarke'), ('Dolan','Mercado'), ('Helen','Guthrie'), ('Elmo','Douglas'), ('Kane','Rice'), 
                      ('Colt','Rowland'), ('John','Rose'), ('Alfonso','Hopkins'), ('Ida','Watts'), ('Jennifer','Coleman'), 
                      ('Ciaran','Newton'), ('Hiram','Carrillo'), ('Devin','Russell'), ('Arsenio','Jensen'),
                      ('Otto','Gibbs'), ('Hiram','Vega'), ('Jarrod','Randolph'), ('Josiah','Gates'), ('Brandon','Stanley'), 
                      ('Kennedy','Nunez'), ('Lewis','Sanchez'), ('Kassie','Chaney'), ('Lance','Knox'), 
                      ('Lamar','Harrison'), ('Honorato','Montgomery'), ('Lisa','Nielsen'), ('Layla','Barr'), ('Nancy','Mcclain'), 
                      ('Kato','Delgado'), ('Todd','Wright'), ('Troy','Mccoy'), ('Gil','Duncan'), ('Lionel','Espinoza') 
                      
                      INSERT INTO SAMPLE_NAME (first_name, last_name)
                      VALUES 
                      ('Otto','Gibbs'), ('Hiram','Vega'), ('Jarrod','Randolph'), ('Josiah','Gates'), ('Brenda','Stanley'), 
                      ('Kennedy','Nunez'), ('Lewis','Sanchez'), ('Ian','Chaney'), ('Stephanie','Knox'), 
                      ('Lamar','Harrison'), ('Honorato','Montgomery'), ('Lisa','Nielsen'), ('Donovan','Barr'), ('Richard','Mcclain'), 
                      ('Donna','Delgado'), ('Todd','Wright'), ('Janice','Mccoy'), ('Gil','Duncan'), ('Lionel','Espinoza'), 
                      ('Jodi','Merrill'), ('Ethan','Vance'), ('Chancellor','Warner'), ('Donna','Wolf'), 
                      ('Betty','Clarke'), ('Dolan','Mercado'), ('Robert','Guthrie'), ('Elmo','Douglas'), ('Susie','Rice'), 
                      ('Colt','Rowland'), ('John','Rose'), ('Alfonso','Hopkins'), ('Quinn','Watts'), ('Maggie','Coleman'), 
                      ('Ciaran','Newton'), ('Hiram','Carrillo'), ('Barb','Russell'), ('Arsenio','Jensen'), ('Carol','Stuart'),
                      ('Otto','Gibbs'), ('Yolanda','Vega'), ('Jarrod','Randolph'), ('Josiah','Gates'), ('Michelle','Stanley'), 
                      ('Kennedy','Nunez'), ('Anna','Sanchez'), ('Ian','Chaney'), ('Michele','Knox'), 
                      ('Lamar','Harrison'), ('Carla','Montgomery'), ('Kennedy','Nielsen'), ('Donovan','Barr'), ('Richard','Mcclain'), 
                      ('Kato','Delgado'), ('Todd','Wright'), ('Troy','Mccoy'), ('Carla','Duncan'), ('Lionel','Espinoza'), 
                      ('Victor','Merrill'), ('Ethan','Vance'), ('Chancellor','Warner'), ('Tina','Wolf'), 
                      ('Carlos','Clarke'), ('Dolan','Mercado'), ('Sherri','Guthrie'), ('Elmo','Douglas'), ('Hope','Rice'), 
                      ('Colt','Rowland'), ('John','Rose'), ('Alfonso','Hopkins'), ('Quinn','Watts'), ('Alden','Coleman'), 
                      ('Ciaran','Newton'), ('Hiram','Carrillo'), ('Devin','Russell'), ('Arsenio','Jensen'), ('Samuel','Stuart'),('Aquila','Mcdowell')

Open in new window

I repeated the process for City-State-Zip…
 
-- CITY-STATE-ZIP
                      
                      /*
                      -- If you have bulk insert privs, or care to create an SSIS pump, you can insert all US zip codes
                      BULK INSERT SAMPLE_CITY_STATE_ZIP
                      FROM 'C:\Documents and Settings\yourname\My Documents\stars\sample\us_zip_codes.txt'
                      WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )
                      */
                      
                      -- All random city-state-zips using http://www.generatedata.com/#generator
                      INSERT INTO SAMPLE_CITY_STATE_ZIP (city,state_code,zip_code) 
                      VALUES 
                      	('Charleston','KS','73262'), ('Palmdale','MS','06568'), ('Phoenix','AZ','68145'), ('Stillwater','TN','92940'), ('Fairmont','OK','31302'), 
                      	('Bell','NM','39446'), ('Everett','OH','59808'), ('Baltimore','WI','20774'), ('Waterbury','DE','20913'), ('Wheeling','NH','85771'), 
                      	('New Bedford','NE','61863'), ('The Dalles','MI','33755'), ('Signal Hill','NE','52010'), ('New Iberia','TN','43899'), ('Anderson','SC','60504'), 
                      	('Sunnyvale','SC','65080'), ('Dodge City','VT','50124'), ('Las Vegas','CT','12059'), ('Chula Vista','CT','07633'), ('Corvallis','MA','60495'), 
                      	('Ketchikan','CA','59300'), ('Milford','CO','27397'), ('Bloomington','NV','75502'), ('New Haven','MI','82988'), ('Slidell','WY','79522'), 
                      	('Eden Prairie','NJ','67114'), ('Port Arthur','ND','39148'), ('Huntington','MD','28115'), ('Bozeman','NC','93590'), ('Dover','AZ','78271'), 
                      	('Coos Bay','TN','20742'), ('Modesto','NM','70909'), ('Florence','NH','89556'), ('Hartford','SC','92104'), ('Cary','RI','35742'), 
                      	('San Dimas','MA','79507'), ('Lafayette','NJ','90540'), ('Rancho Palos Verdes','NM','78112'), ('Oxnard','VA','61248'), ('Cranston','WY','45254'), 
                      	('Greenfield','CO','85503'), ('Kingsport','DE','84470'), ('Minot','AZ','24329'), ('Torrington','MS','86325'), ('West Lafayette','SD','26895'), 
                      	('Cranston','ME','93835'), ('Fort Worth','PA','62701'), ('Eden Prairie','MD','31464'), ('Clarksville','MS','67997'), ('Gainesville','SC','18735'), 
                      	('Alamogordo','DE','50453'), ('Orangeburg','AK','25592'), ('Georgetown','VT','20211'), ('Brooklyn Park','TX','86292'), ('Eureka','MI','18720'), 
                      	('Dover','RI','08610'), ('Pasco','MD','59218'), ('Warner Robins','MN','72933'), ('Saint Cloud','CA','33774'), ('Manhattan Beach','MN','10695'), 
                      	('Norton','ID','11239'), ('Madison','KS','03487'), ('Somerville','NM','19961'), ('Woodward','LA','55347'), ('Laguna Woods','PA','21712'), 
                      	('Bossier City','NY','47379'), ('Florence','OR','79429'), ('Saint Joseph','NE','01144'), ('Fernley','CO','12707'), ('Leominster','KY','62009'), 
                      	('West Hollywood','MT','27357'), ('Lodi','ND','92554'), ('Salisbury','IL','77914'), ('Dana Point','DC','10303'), ('Beaver Falls','LA','85985'), 
                      	('Clairton','LA','13060'), ('Stamford','NY','84795'), ('Savannah','KY','49927'), ('Maywood','DE','39190'), ('Kingston','WY','83712'), 
                      	('Columbia','WV','75289'), ('Cortland','UT','43546'), ('Marshall','NM','05363'), ('Providence','ME','55122'), ('Paducah','NH','70774'), 
                      	('Boulder Junction','KY','85256'), ('Sioux City','UT','56751'), ('Fontana','NC','30798'), ('Sandpoint','OR','89257'), ('Santa Cruz','UT','71797'), 
                      	('Georgetown','OR','09062'), ('Beloit','KY','19448'), ('Huntsville','NC','42499'), ('Oklahoma City','NM','96646'), ('New Rochelle','MO','55047'), 
                      	('Bangor','WV','38252'), ('Lakewood','AZ','78217'), ('Tupelo','SD','47732'), ('Hanahan','MA','47230'), ('Biddeford','NV','07544')
                      
                      INSERT INTO SAMPLE_CITY_STATE_ZIP (city,state_code,zip_code) 
                      VALUES 
                      	('Green Bay','PA','63327'), ('Auburn Hills','WI','35049'), ('Seal Beach','OK','88519'), ('Elkhart','OK','01218'), ('Morgan City','MO','59483'), 
                      	('Akron','WI','75373'), ('Corona','IL','52522'), ('Oneonta','MI','09445'), ('Lawrence','VT','93691'), ('Cedarburg','HI','41956'), 
                      	('Council Bluffs','AZ','73913'), ('Waco','ND','16805'), ('Citrus Heights','NY','77121'), ('Farmer City','HI','47894'), ('Butte','VA','83224'), 
                      	('Uniontown','CA','21958'), ('Oro Valley','PA','03498'), ('Chico','SC','96245'), ('Manassas','NY','58401'), ('Murrieta','AL','64645'), 
                      	('Ada','VA','03562'), ('Delta Junction','VT','42548'), ('Hoboken','VT','49295'), ('Rockford','NM','69840'), ('Chester','WY','25090'), 
                      	('New Prague','MN','83138'), ('Alpharetta','NC','44670'), ('Nevada City','NE','74316'), ('Wahoo','DC','50379'), ('Oxford','TX','89243'), 
                      	('Pawtucket','CO','76133'), ('Moline','OK','66851'), ('Walla Walla','WA','67105'), ('Beloit','ME','01578'), ('Chattanooga','VA','49976'), 
                      	('Manitowoc','NE','41527'), ('Mackinac Island','LA','42983'), ('Berlin','MA','50205'), ('Decatur','WI','57012'), ('Des Moines','OR','98882'), 
                      	('Caguas','RI','85581'), ('Miami Gardens','AK','21216'), ('Attleboro','HI','49757'), ('Macon','KS','42990'), ('Macomb','MI','09802'), 
                      	('Bell Gardens','KY','36660'), ('East Hartford','KY','88363'), ('Marlborough','NJ','16576'), ('Lancaster','NC','71155'), ('Sandpoint','DE','44423'), 
                      	('Fresno','NV','05877'), ('Mesquite','GA','61048'), ('Camden','NJ','35729'), ('Fallon','OR','07668'), ('Dickinson','NV','17434'), 
                      	('Moorhead','ME','55819'), ('Joplin','WV','29689'), ('Highland Park','MT','72863'), ('Niagara Falls','CO','60015'), ('Melrose','AZ','46688'), 
                      	('Minot','MO','82110'), ('Hermitage','WY','68375'), ('Keene','PA','37772'), ('Hoboken','MD','96054'), ('Georgetown','OH','06072'), 
                      	('Washington','DC','67060'), ('Sioux City','KY','71101'), ('San Gabriel','AK','43974'), ('Louisville','TN','73619'), ('Hollywood','GA','09116'), 
                      	('Hartford','IA','07231'), ('Muskegon','IL','77254'), ('Norman','GA','57298'), ('Chandler','FL','52503'), ('El Segundo','KS','99397'), 
                      	('Beverly','GA','84140'), ('Temple City','ND','82667'), ('Washington','DC','41881'), ('Statesboro','FL','22881'), ('Gold Beach','WA','82450'), 
                      	('Peabody','NC','09323'), ('Mechanicville','DE','03766'), ('Wisconsin Rapids','MO','83621'), ('Westminster','OH','38889'), ('Portsmouth','GA','73243'), 
                      	('Nanticoke','CO','04712'), ('Bowie','VT','81095'), ('Tallahassee','OR','46953'), ('Durham','OR','38459'), ('Vicksburg','IA','30669'), 
                      	('Tok','OH','55830'), ('Madison','KS','52616'), ('Mobile','NM','54778'), ('Sandy','MI','32588'), ('Charleston','OH','25711'), 
                      	('Yigo','SD','38794'), ('Eugene','NC','46719'), ('Areceibo','PA','94106'), ('Pasadena','SD','86049'), ('Tuscaloosa','GA','45460')

Open in new window

… and address, although I removed the house numbers in favor of using them later in code.

-- STREET
                      INSERT INTO SAMPLE_STREET (addr1, addr2)
                      VALUES 
                      	('Shasta Way', '109'), ('Park Ln', '3'), ('Michigan Avenue', '3'), ('Ann St', 'A333'), ('Bailey St', 'Apt 6a'), 
                      	('Collingwood Dr', 'Apt 4B'), ('W Circle Dr', '#205'), ('Grand River Avenue', '#231'), ('Charles St', 'Apt 3B'), ('Beech St', '#109'), 
                      	('Burcham Dr', '112'), ('Albert Ave', '120'), ('Elisabeth St', '309'), ('Dormitory Rd', 'N316'), ('Snyder Road', 'A105'), 
                      	('Kedzie St', '332'), ('Orchard St', ''), ('River St', ''), ('Cedar St', 'Apt #1C'), ('Rogue St', '121'), 
                      	('Truscott St.', '32'), ('Trowbridge Rd', '99'), ('Wilson Road', ''), ('Shaw Lane', '109'), ('P.O. Box', '119'), 
                      	('P.O. Box', '10'), ('P.O. Box 635', '150'), ('PO Box', ''), ('Red Cedar Road', ''), ('Hassinger Road', ''), 
                      	('Century Oaks Way', '34'), ('Century Meadow Ct', '44'), ('Pebbletree Way', ''), ('Northgate Dr', '2C'), ('Crosslees Dr', ''), 
                      	('South Hwy 101', '108'), ('West County Road D', ''), ('William St', ''), ('Water St', ''), ('Trinity Place', ''), 
                      	('Paseo De Peralta', '33'), ('W Buena Vista St', ''), ('Canyon Road', ''), ('Mountain View Lane', '6D'), ('Forest View Rd', '#203'), 
                      	('Coronado Road', '4a'), ('Old Santa Fe Trail', '5A'), ('Linda Vista Rd', '145'), ('Regents Park', 'C205'), ('Thousand Oaks Dr', 'C105'), 
                      	('Spyglass Plaza', '217'), ('Rae Dell Ave', '155'), ('Annie St. W', ''), ('Union St', '34'), ('Hammond St', ''), 
                      	('Silver Road', '155'), ('Webster Ave', '155'), ('Buck St', '155'), ('Stillwater Ave', ''), ('Mt Hope Ave', 'A558'), 
                      	('State St', '155'), ('N Main St', ''), ('Essex St', 'A108'), ('Yale St', 'A256'), ('Harvard St', 'Apt 501c'), 
                      	('Valley View Road', '155'), ('Anderson Lakes Parkway', '33'), ('Bloomington Ferry Road', '23'), ('Watchmaker St', '52'), ('Kirkwood Ave', '21'), 
                      	('Wedgewood Ave', '33'), ('Franklin Place', '121'), ('Thompson Ln', '22'), ('Pioneer Trail', '280'), ('France Ave', 'Apt 6D'), 
                      	('Broadland Cove', 'B123'), ('Woodland Valley', '112'), ('Whitefish Way', '107'), ('Eisenhower Road', '105'), ('Raymond Heights Road', '101')

Open in new window

Then let’s create our final SAMPLE_CUSTOMER table, and populate it using a loop with a million blank rows.

-- 2-Populate the final customer table
                      -- Insert one million rows (learned from Itzik Ben-Gan presentation on SQL 2008 Tips and Tricks)
                      IF OBJECT_ID('SAMPLE_CUSTOMER') IS NOT NULL
                      	DROP TABLE SAMPLE_CUSTOMER
                      
                      CREATE TABLE SAMPLE_CUSTOMER (
                      	id int IDENTITY(1,1) PRIMARY KEY, 
                      	no varchar(25), 
                      	first_name varchar(50), 
                      	last_name varchar(50), 
                      	mi char(1),					
                      	dob date,								
                      	ssn varchar(9),
                      	addr1 varchar(100), 
                      	addr2 varchar(100),
                      	city varchar(50), 
                      	state_code char(2), 
                      	zip_code varchar(5),
                      
                      	phone_num varchar(15))	
                      Declare @i int = 1
                      
                      INSERT INTO SAMPLE_CUSTOMER (no) VALUES ('')
                      WHILE @i <= 20 BEGIN
                      
                      	INSERT INTO SAMPLE_CUSTOMER (no)
                      	SELECT no FROM SAMPLE_CUSTOMER
                      
                          SET @i = @i + 1
                          
                      END
                      
                      -- Shave off the excess to make it an even million
                      DELETE FROM SAMPLE_CUSTOMER WHERE id > 1000000

Open in new window

 Now let’s set up a cursor where we will go through each row, and choose random values from random columns in our three lookup tables.   Note the word random.  This code is so randomized that the columns have no bearing on each other, meaning it is quite possible that a row will be Haqquisha Goldstein, living in Los Angeles, NY, with a phone number with an area code that is who knows where.  

First, I'll save the rowcounts of my three list tables into variables
-- Get the max id of various tables
                      Declare @max_name int 
                      SELECT @max_name = COUNT(*) FROM SAMPLE_NAME
                      
                      Declare @max_address int 
                      SELECT @max_address = COUNT(*) FROM SAMPLE_STREET
                      
                      Declare @max_csz int
                      SELECT @max_csz = COUNT(*) FROM SAMPLE_CITY_STATE_ZIP 

Open in new window

Then start the cursor

-- Cursor variables used everywhere
                      DECLARE 	
                      	@id int, @no varchar(25), @first_name varchar(50), @last_name varchar(50), @mi char(1),
                      	@dob date, @ssn char(9), @addr1 varchar(100),  @addr2 varchar(100), 
                      	@city varchar(30), @state_code char(2), @zip_code varchar(9),  @phone_num varchar(15) 
                      
                      -- Variables used to support random numbers
                      DECLARE 
                      	@min bigint, @max bigint, @rand as bigint, @rand1 as bigint, @rand2 as bigint, @rand3 as bigint
                      
                      -- One million person cursor
                      
                      DECLARE cur Cursor 
                      FOR 
                      SELECT 
                      	id, no, first_name, last_name, mi, 
                      	dob, ssn, addr1, addr2, 
                      	city, state_code, zip_code, phone_num	
                      FROM SAMPLE_CUSTOMER
                      
                      Open cur 
                      
                      Fetch NEXT FROM cur INTO 
                      	@id, @no, @first_name, @last_name, @mi, 
                      	@dob, @ssn, @addr1, @addr2, 
                      	@city, @state_code, @zip_code, @phone_num	
                      While (@@FETCH_STATUS <> -1)  
                      BEGIN
                      IF (@@FETCH_STATUS <> -2)

Open in new window

The Rand() function creates a random number between 0 to 1 with 15 decimal places.  

To generate a random whole number between a given min and max...
RAND() + Min and Max values of your choice + assigning to an int to round off numbers

To generate a random letter between a given min and max (say A and Z)
CHAR(64 + Same as above, with numeric Min and Max values, say A=1, Z=26)

 
-- no:  Generate a random CustomerCode varchar(25) in format AAA####### 
                      SELECT @min = 1, @max = 26
                      SELECT @rand1 = ((@max + 1) - @min) * Rand() + @min 
                      SELECT @rand2 = ((@max + 1) - @min) * Rand() + @min 
                      SELECT @rand3 = ((@max + 1) - @min) * Rand() + @min 
                      
                      SELECT @min = 1, @max = 9999999
                      SELECT @rand = ((@max + 1) - @min) * Rand() + @min 
                      
                      SELECT @no = CHAR(64 + @rand1) + CHAR(64 + @rand2) + CHAR(64 + @rand3) + CAST(@rand as varchar(7))

Open in new window

 First and last names:  Since the id field is an autonumber between zero and one million, Rand() works by rounding and using the row count.

-- first_name
                      SELECT @first_name = first_name FROM SAMPLE_NAME WHERE id = ROUND(@max_name * rand(), 0)
                      
                      -- last_name
                      SELECT @last_name = last_name FROM SAMPLE_NAME WHERE id = ROUND(@max_name * rand(), 0)

Open in new window

Middle initial:  Same concept as above, but here I have another Rand() function to kick out a number between 0 and 100, and only populate the column for 26% of all rows.

-- mi, Random Middle initial, 26% of column has a middle initial, 74% do not. 
                      SELECT @min = 1, @max = 100
                      SELECT @rand = ((@max + 1) - @min) * Rand() + @min 
                      
                      IF @rand > 26
                      	SET @mi = NULL
                      ELSE
                      	SET @mi = CHAR(64 + @rand)

Open in new window

Date of Birth:  Create a random date..

-- dob: Generate a random date 
                      SELECT @dob = dateadd(month, -1 * abs(convert(varbinary, newid()) % (90 * 12)), getdate()) 

Open in new window

SSN:  JUst a randome nine digit number.  Piece of cake.

-- SSN - Random nine-digit number
                      SELECT @min = 1, @max = 999999999
                      Select @ssn = CAST(CAST(ROUND(((@max + 1) - @min) * Rand() + @min,0) as int)as varchar(9))

Open in new window

Address line #1:  A random number from 1 to 10000 for the house number, followed by a random street address.

-- addr1, House number - Random four-digit number + addr1
                      SELECT @min = 1, @max = 9999
                      Select @addr1 = CAST(ROUND(((@max + 1) - @min) * Rand() + @min,0) as varchar(4)) + ' ' + addr1 FROM SAMPLE_STREET WHERE id = ROUND(@max_address * rand(), 0)

Open in new window

Address like #2:  For 5% of all rows populate a random Address 2 line

-- addr2:  5% of all addresses have a second address line, 95% do not. 
                      SELECT @min = 1, @max = 100
                      SELECT @rand = ((@max + 1) - @min) * Rand() + @min 
                      
                      IF @rand > 5
                      	SET @mi = NULL
                      ELSE
                      	SELECT @addr2 = addr2 FROM SAMPLE_STREET WHERE id = ROUND(@max_address * rand(), 0)

Open in new window

City, State, Zip:  Random values from the list table.  

-- city
                      SELECT @city = city FROM SAMPLE_CITY_STATE_ZIP  WHERE id = ROUND(@max_csz* rand(), 0)
                      
                      -- state
                      SELECT @state_code = state_code FROM SAMPLE_CITY_STATE_ZIP  WHERE id = ROUND(@max_csz* rand(), 0)
                      
                      -- zip
                      SELECT @zip_code= zip_code FROM SAMPLE_CITY_STATE_ZIP  WHERE id = ROUND(@max_csz* rand(), 0)

Open in new window

Phone number:  Random 10 digit number.  Normally you wouldn't include the parentheses and dash, but I had to include it for a previous project.

-- Phone - Random ten-digit number
                      SELECT @min = 1, @max = 9999999999
                      Select @phone_num = CAST(CAST(((@max + 1) - @min) * Rand() + @min as bigint) as varchar(15))
                      SELECT @phone_num = '(' + LEFT(@phone_num, 3) + ') ' + SUBSTRING(@phone_num, 4, 3) + '-' + RIGHT(@phone_num,4)

Open in new window

Then update the cursor.

UPDATE SAMPLE_CUSTOMER
                      SET
                      	no = @no, first_name = @first_name, last_name = @last_name, mi = @mi, 
                      	dob = @dob, ssn = @ssn, addr1 = @addr1, addr2 = @addr2, 
                      	city = @city, state_code = @state_code, zip_code = @zip_code, phone_num = @phone_num	
                      WHERE current of cur
                      
                      FETCH NEXT FROM cur INTO 	
                      	@id, @no, @first_name, @last_name, @mi, 
                      	@dob, @ssn, @addr1, @addr2, 
                      	@city, @state_code, @zip_code, @phone_num	
                      END
                      CLOSE cur
                      DEALLOCATE cur

Open in new window

The full code is here

/*
                      Used to create SAMPLE PEOPLE tables for dummy people. 
                      
                      07-28-13  Jim Horn   Original for demo purposes only.   Can use in any applicaiton as long as this line is unchanged. 
                      
                      STEPS
                      
                      1-Drop-Create-Pouplate list tables
                      * SAMPLE_NAME
                      * SAMPLE_CITY_STATE_ZIP
                      * SAMPLE_STREET
                      
                      2-Populate the final customer table
                      * SAMPLE_CUSTOMER
                      
                      */
                      
                      -- 1-Drop list tables (if they currently exist)
                      
                      IF OBJECT_ID('SAMPLE_NAME') IS NOT NULL
                      	DROP TABLE SAMPLE_NAME
                      
                      IF OBJECT_ID('SAMPLE_CITY_STATE_ZIP') IS NOT NULL
                      	DROP TABLE SAMPLE_CITY_STATE_ZIP
                      
                      IF OBJECT_ID('SAMPLE_STREET') IS NOT NULL
                      	DROP TABLE SAMPLE_STREET
                      
                      -- 1-Create list tables
                      
                      CREATE TABLE SAMPLE_NAME (
                      	id int IDENTITY(1,1) PRIMARY KEY,  
                      	first_name varchar(50), 
                      	last_name varchar(50))
                      
                      CREATE TABLE SAMPLE_CITY_STATE_ZIP (
                      	id int IDENTITY(1,1) PRIMARY KEY, 
                      	city varchar(50), 
                      	state_code char(2), 
                      	zip_code varchar(5))
                      
                      CREATE TABLE SAMPLE_STREET (
                      	id int IDENTITY(1,1) PRIMARY KEY, 
                      	addr1 varchar(100), 
                      	addr2 varchar(100))		
                      
                      -- 1-Populate list tables	
                      
                      -- NAMES:  Generated using http://www.generatedata.com/#generator
                      INSERT INTO SAMPLE_NAME (first_name, last_name)
                      VALUES 
                      ('Janet', 'Smith'), ('Frank', 'Bloswick'), ('Tonya', 'Bazinaw'), ('Mike', 'St. Onge'),
                      ('Jackie', 'Jones'), ('Darren', 'Tillbrooke'),('Stephanie', 'Holsinger'), ('Rene', 'Hughey'),
                      ('Robert', 'Rogers'), ('Richard', 'LaPine'), ('Kathy', 'Summerfield'), ('Kathy', 'Bodwin'),
                      ('Mitch', 'Krause'), ('George', 'Dow'), ('Jack', 'Malone'), ('Bill', 'Schweiz'),
                      ('Mark', 'Gunter'), ('Bob', 'Anderson'),('Scott', 'Simpson'), ('Phil', 'Dingman'),
                      ('Chad', 'Leiker'), ('Ian', 'Benson'), ('Nicole', 'Lane'), ('Steve', 'Lundeen'),
                      ('Erica','Black'), ('Xenos','Mathis'), ('Kyle','Good'), ('Raja','Dejesus'), ('Timothy','Frazier'), 
                      ('Francine','Morrison'), ('Avram','Pate'), ('Hammett','Coffey'), ('Hasad','Wise'), ('Cullen','Riddle'), 
                      ('Kato','Delgado'), ('Todd','Wright'), ('Troy','Mccoy'), ('Gil','Duncan'), ('Lionel','Espinoza'), 
                      ('Victor','Merrill'), ('Gennifer','Vance'), ('Chancellor','Warner'), ('Davis','Wolf'), 
                      ('Carlos','Clarke'), ('Dolan','Mercado'), ('Helen','Guthrie'), ('Elmo','Douglas'), ('Kane','Rice'), 
                      ('Colt','Rowland'), ('John','Rose'), ('Alfonso','Hopkins'), ('Ida','Watts'), ('Jennifer','Coleman'), 
                      ('Ciaran','Newton'), ('Hiram','Carrillo'), ('Devin','Russell'), ('Arsenio','Jensen'),
                      ('Otto','Gibbs'), ('Hiram','Vega'), ('Jarrod','Randolph'), ('Josiah','Gates'), ('Brandon','Stanley'), 
                      ('Kennedy','Nunez'), ('Lewis','Sanchez'), ('Kassie','Chaney'), ('Lance','Knox'), 
                      ('Lamar','Harrison'), ('Honorato','Montgomery'), ('Lisa','Nielsen'), ('Layla','Barr'), ('Nancy','Mcclain'), 
                      ('Kato','Delgado'), ('Todd','Wright'), ('Troy','Mccoy'), ('Gil','Duncan'), ('Lionel','Espinoza') 
                      
                      INSERT INTO SAMPLE_NAME (first_name, last_name)
                      VALUES 
                      ('Otto','Gibbs'), ('Hiram','Vega'), ('Jarrod','Randolph'), ('Josiah','Gates'), ('Brenda','Stanley'), 
                      ('Kennedy','Nunez'), ('Lewis','Sanchez'), ('Ian','Chaney'), ('Stephanie','Knox'), 
                      ('Lamar','Harrison'), ('Honorato','Montgomery'), ('Lisa','Nielsen'), ('Donovan','Barr'), ('Richard','Mcclain'), 
                      ('Donna','Delgado'), ('Todd','Wright'), ('Janice','Mccoy'), ('Gil','Duncan'), ('Lionel','Espinoza'), 
                      ('Jodi','Merrill'), ('Ethan','Vance'), ('Chancellor','Warner'), ('Donna','Wolf'), 
                      ('Betty','Clarke'), ('Dolan','Mercado'), ('Robert','Guthrie'), ('Elmo','Douglas'), ('Susie','Rice'), 
                      ('Colt','Rowland'), ('John','Rose'), ('Alfonso','Hopkins'), ('Quinn','Watts'), ('Maggie','Coleman'), 
                      ('Ciaran','Newton'), ('Hiram','Carrillo'), ('Barb','Russell'), ('Arsenio','Jensen'), ('Carol','Stuart'),
                      ('Otto','Gibbs'), ('Yolanda','Vega'), ('Jarrod','Randolph'), ('Josiah','Gates'), ('Michelle','Stanley'), 
                      ('Kennedy','Nunez'), ('Anna','Sanchez'), ('Ian','Chaney'), ('Michele','Knox'), 
                      ('Lamar','Harrison'), ('Carla','Montgomery'), ('Kennedy','Nielsen'), ('Donovan','Barr'), ('Richard','Mcclain'), 
                      ('Kato','Delgado'), ('Todd','Wright'), ('Troy','Mccoy'), ('Carla','Duncan'), ('Lionel','Espinoza'), 
                      ('Victor','Merrill'), ('Ethan','Vance'), ('Chancellor','Warner'), ('Tina','Wolf'), 
                      ('Carlos','Clarke'), ('Dolan','Mercado'), ('Sherri','Guthrie'), ('Elmo','Douglas'), ('Hope','Rice'), 
                      ('Colt','Rowland'), ('John','Rose'), ('Alfonso','Hopkins'), ('Quinn','Watts'), ('Alden','Coleman'), 
                      ('Ciaran','Newton'), ('Hiram','Carrillo'), ('Devin','Russell'), ('Arsenio','Jensen'), ('Samuel','Stuart'),('Aquila','Mcdowell')
                      
                      
                      -- CITY-STATE-ZIP
                      
                      /*
                      -- If you have bulk insert privs, or care to create an SSIS pump, you can insert all US zip codes
                      BULK INSERT SAMPLE_CITY_STATE_ZIP
                      FROM 'C:\Documents and Settings\yourname\My Documents\stars\sample\us_zip_codes.txt'
                      WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )
                      */
                      
                      -- All random city-state-zips using http://www.generatedata.com/#generator
                      INSERT INTO SAMPLE_CITY_STATE_ZIP (city,state_code,zip_code) 
                      VALUES 
                      	('Charleston','KS','73262'), ('Palmdale','MS','06568'), ('Phoenix','AZ','68145'), ('Stillwater','TN','92940'), ('Fairmont','OK','31302'), 
                      	('Bell','NM','39446'), ('Everett','OH','59808'), ('Baltimore','WI','20774'), ('Waterbury','DE','20913'), ('Wheeling','NH','85771'), 
                      	('New Bedford','NE','61863'), ('The Dalles','MI','33755'), ('Signal Hill','NE','52010'), ('New Iberia','TN','43899'), ('Anderson','SC','60504'), 
                      	('Sunnyvale','SC','65080'), ('Dodge City','VT','50124'), ('Las Vegas','CT','12059'), ('Chula Vista','CT','07633'), ('Corvallis','MA','60495'), 
                      	('Ketchikan','CA','59300'), ('Milford','CO','27397'), ('Bloomington','NV','75502'), ('New Haven','MI','82988'), ('Slidell','WY','79522'), 
                      	('Eden Prairie','NJ','67114'), ('Port Arthur','ND','39148'), ('Huntington','MD','28115'), ('Bozeman','NC','93590'), ('Dover','AZ','78271'), 
                      	('Coos Bay','TN','20742'), ('Modesto','NM','70909'), ('Florence','NH','89556'), ('Hartford','SC','92104'), ('Cary','RI','35742'), 
                      	('San Dimas','MA','79507'), ('Lafayette','NJ','90540'), ('Rancho Palos Verdes','NM','78112'), ('Oxnard','VA','61248'), ('Cranston','WY','45254'), 
                      	('Greenfield','CO','85503'), ('Kingsport','DE','84470'), ('Minot','AZ','24329'), ('Torrington','MS','86325'), ('West Lafayette','SD','26895'), 
                      	('Cranston','ME','93835'), ('Fort Worth','PA','62701'), ('Eden Prairie','MD','31464'), ('Clarksville','MS','67997'), ('Gainesville','SC','18735'), 
                      	('Alamogordo','DE','50453'), ('Orangeburg','AK','25592'), ('Georgetown','VT','20211'), ('Brooklyn Park','TX','86292'), ('Eureka','MI','18720'), 
                      	('Dover','RI','08610'), ('Pasco','MD','59218'), ('Warner Robins','MN','72933'), ('Saint Cloud','CA','33774'), ('Manhattan Beach','MN','10695'), 
                      	('Norton','ID','11239'), ('Madison','KS','03487'), ('Somerville','NM','19961'), ('Woodward','LA','55347'), ('Laguna Woods','PA','21712'), 
                      	('Bossier City','NY','47379'), ('Florence','OR','79429'), ('Saint Joseph','NE','01144'), ('Fernley','CO','12707'), ('Leominster','KY','62009'), 
                      	('West Hollywood','MT','27357'), ('Lodi','ND','92554'), ('Salisbury','IL','77914'), ('Dana Point','DC','10303'), ('Beaver Falls','LA','85985'), 
                      	('Clairton','LA','13060'), ('Stamford','NY','84795'), ('Savannah','KY','49927'), ('Maywood','DE','39190'), ('Kingston','WY','83712'), 
                      	('Columbia','WV','75289'), ('Cortland','UT','43546'), ('Marshall','NM','05363'), ('Providence','ME','55122'), ('Paducah','NH','70774'), 
                      	('Boulder Junction','KY','85256'), ('Sioux City','UT','56751'), ('Fontana','NC','30798'), ('Sandpoint','OR','89257'), ('Santa Cruz','UT','71797'), 
                      	('Georgetown','OR','09062'), ('Beloit','KY','19448'), ('Huntsville','NC','42499'), ('Oklahoma City','NM','96646'), ('New Rochelle','MO','55047'), 
                      	('Bangor','WV','38252'), ('Lakewood','AZ','78217'), ('Tupelo','SD','47732'), ('Hanahan','MA','47230'), ('Biddeford','NV','07544')
                      
                      INSERT INTO SAMPLE_CITY_STATE_ZIP (city,state_code,zip_code) 
                      VALUES 
                      	('Green Bay','PA','63327'), ('Auburn Hills','WI','35049'), ('Seal Beach','OK','88519'), ('Elkhart','OK','01218'), ('Morgan City','MO','59483'), 
                      	('Akron','WI','75373'), ('Corona','IL','52522'), ('Oneonta','MI','09445'), ('Lawrence','VT','93691'), ('Cedarburg','HI','41956'), 
                      	('Council Bluffs','AZ','73913'), ('Waco','ND','16805'), ('Citrus Heights','NY','77121'), ('Farmer City','HI','47894'), ('Butte','VA','83224'), 
                      	('Uniontown','CA','21958'), ('Oro Valley','PA','03498'), ('Chico','SC','96245'), ('Manassas','NY','58401'), ('Murrieta','AL','64645'), 
                      	('Ada','VA','03562'), ('Delta Junction','VT','42548'), ('Hoboken','VT','49295'), ('Rockford','NM','69840'), ('Chester','WY','25090'), 
                      	('New Prague','MN','83138'), ('Alpharetta','NC','44670'), ('Nevada City','NE','74316'), ('Wahoo','DC','50379'), ('Oxford','TX','89243'), 
                      	('Pawtucket','CO','76133'), ('Moline','OK','66851'), ('Walla Walla','WA','67105'), ('Beloit','ME','01578'), ('Chattanooga','VA','49976'), 
                      	('Manitowoc','NE','41527'), ('Mackinac Island','LA','42983'), ('Berlin','MA','50205'), ('Decatur','WI','57012'), ('Des Moines','OR','98882'), 
                      	('Caguas','RI','85581'), ('Miami Gardens','AK','21216'), ('Attleboro','HI','49757'), ('Macon','KS','42990'), ('Macomb','MI','09802'), 
                      	('Bell Gardens','KY','36660'), ('East Hartford','KY','88363'), ('Marlborough','NJ','16576'), ('Lancaster','NC','71155'), ('Sandpoint','DE','44423'), 
                      	('Fresno','NV','05877'), ('Mesquite','GA','61048'), ('Camden','NJ','35729'), ('Fallon','OR','07668'), ('Dickinson','NV','17434'), 
                      	('Moorhead','ME','55819'), ('Joplin','WV','29689'), ('Highland Park','MT','72863'), ('Niagara Falls','CO','60015'), ('Melrose','AZ','46688'), 
                      	('Minot','MO','82110'), ('Hermitage','WY','68375'), ('Keene','PA','37772'), ('Hoboken','MD','96054'), ('Georgetown','OH','06072'), 
                      	('Washington','DC','67060'), ('Sioux City','KY','71101'), ('San Gabriel','AK','43974'), ('Louisville','TN','73619'), ('Hollywood','GA','09116'), 
                      	('Hartford','IA','07231'), ('Muskegon','IL','77254'), ('Norman','GA','57298'), ('Chandler','FL','52503'), ('El Segundo','KS','99397'), 
                      	('Beverly','GA','84140'), ('Temple City','ND','82667'), ('Washington','DC','41881'), ('Statesboro','FL','22881'), ('Gold Beach','WA','82450'), 
                      	('Peabody','NC','09323'), ('Mechanicville','DE','03766'), ('Wisconsin Rapids','MO','83621'), ('Westminster','OH','38889'), ('Portsmouth','GA','73243'), 
                      	('Nanticoke','CO','04712'), ('Bowie','VT','81095'), ('Tallahassee','OR','46953'), ('Durham','OR','38459'), ('Vicksburg','IA','30669'), 
                      	('Tok','OH','55830'), ('Madison','KS','52616'), ('Mobile','NM','54778'), ('Sandy','MI','32588'), ('Charleston','OH','25711'), 
                      	('Yigo','SD','38794'), ('Eugene','NC','46719'), ('Areceibo','PA','94106'), ('Pasadena','SD','86049'), ('Tuscaloosa','GA','45460')
                      	
                      	
                      -- STREET
                      INSERT INTO SAMPLE_STREET (addr1, addr2)
                      VALUES 
                      	('Shasta Way', '109'), ('Park Ln', '3'), ('Michigan Avenue', '3'), ('Ann St', 'A333'), ('Bailey St', 'Apt 6a'), 
                      	('Collingwood Dr', 'Apt 4B'), ('W Circle Dr', '#205'), ('Grand River Avenue', '#231'), ('Charles St', 'Apt 3B'), ('Beech St', '#109'), 
                      	('Burcham Dr', '112'), ('Albert Ave', '120'), ('Elisabeth St', '309'), ('Dormitory Rd', 'N316'), ('Snyder Road', 'A105'), 
                      	('Kedzie St', '332'), ('Orchard St', ''), ('River St', ''), ('Cedar St', 'Apt #1C'), ('Rogue St', '121'), 
                      	('Truscott St.', '32'), ('Trowbridge Rd', '99'), ('Wilson Road', ''), ('Shaw Lane', '109'), ('P.O. Box', '119'), 
                      	('P.O. Box', '10'), ('P.O. Box 635', '150'), ('PO Box', ''), ('Red Cedar Road', ''), ('Hassinger Road', ''), 
                      	('Century Oaks Way', '34'), ('Century Meadow Ct', '44'), ('Pebbletree Way', ''), ('Northgate Dr', '2C'), ('Crosslees Dr', ''), 
                      	('South Hwy 101', '108'), ('West County Road D', ''), ('William St', ''), ('Water St', ''), ('Trinity Place', ''), 
                      	('Paseo De Peralta', '33'), ('W Buena Vista St', ''), ('Canyon Road', ''), ('Mountain View Lane', '6D'), ('Forest View Rd', '#203'), 
                      	('Coronado Road', '4a'), ('Old Santa Fe Trail', '5A'), ('Linda Vista Rd', '145'), ('Regents Park', 'C205'), ('Thousand Oaks Dr', 'C105'), 
                      	('Spyglass Plaza', '217'), ('Rae Dell Ave', '155'), ('Annie St. W', ''), ('Union St', '34'), ('Hammond St', ''), 
                      	('Silver Road', '155'), ('Webster Ave', '155'), ('Buck St', '155'), ('Stillwater Ave', ''), ('Mt Hope Ave', 'A558'), 
                      	('State St', '155'), ('N Main St', ''), ('Essex St', 'A108'), ('Yale St', 'A256'), ('Harvard St', 'Apt 501c'), 
                      	('Valley View Road', '155'), ('Anderson Lakes Parkway', '33'), ('Bloomington Ferry Road', '23'), ('Watchmaker St', '52'), ('Kirkwood Ave', '21'), 
                      	('Wedgewood Ave', '33'), ('Franklin Place', '121'), ('Thompson Ln', '22'), ('Pioneer Trail', '280'), ('France Ave', 'Apt 6D'), 
                      	('Broadland Cove', 'B123'), ('Woodland Valley', '112'), ('Whitefish Way', '107'), ('Eisenhower Road', '105'), ('Raymond Heights Road', '101') 
                      
                      
                      -- 2-Populate the final customer table
                      -- Insert one million rows (learned from Itzik Ben-Gan presentation on SQL 2008 Tips and Tricks)
                      
                      IF OBJECT_ID('SAMPLE_CUSTOMER') IS NOT NULL
                      	DROP TABLE SAMPLE_CUSTOMER
                      
                      CREATE TABLE SAMPLE_CUSTOMER (
                      	id int IDENTITY(1,1) PRIMARY KEY, 
                      	no varchar(25), 
                      	first_name varchar(50), 
                      	last_name varchar(50), 
                      	mi char(1),					
                      	dob date,					
                      	ssn varchar(9),
                      	addr1 varchar(100), 
                      	addr2 varchar(100),
                      	city varchar(50), 
                      	state_code char(2), 
                      	zip_code varchar(5),
                      
                      	phone_num varchar(15))	
                      Declare @i int = 1
                      
                      INSERT INTO SAMPLE_CUSTOMER (no) VALUES ('')
                      WHILE @i <= 20 BEGIN
                      
                      	INSERT INTO SAMPLE_CUSTOMER (no)
                      	SELECT no FROM SAMPLE_CUSTOMER
                      
                          SET @i = @i + 1
                          
                      END
                      
                      -- Shave off the excess to make it an even million
                      DELETE FROM SAMPLE_CUSTOMER WHERE id > 1000000
                      
                      -- Get the max id of various tables
                      Declare @max_name int 
                      SELECT @max_name = COUNT(*) FROM SAMPLE_NAME
                      
                      Declare @max_address int 
                      SELECT @max_address = COUNT(*) FROM SAMPLE_STREET
                      
                      Declare @max_csz int
                      SELECT @max_csz = COUNT(*) FROM SAMPLE_CITY_STATE_ZIP 
                      
                      -- Cursor variables used everywhere
                      DECLARE 	
                      	@id int, @no varchar(25), @first_name varchar(50), @last_name varchar(50), @mi char(1),
                      	@dob date, @ssn char(9), @addr1 varchar(100),  @addr2 varchar(100), 
                      	@city varchar(30), @state_code char(2), @zip_code varchar(9),  @phone_num varchar(15) 
                      
                      -- Variables used to support random numbers
                      DECLARE 
                      	@min bigint, @max bigint, @rand as bigint, @rand1 as bigint, @rand2 as bigint, @rand3 as bigint
                      
                      -- One million person cursor
                      
                      DECLARE cur Cursor 
                      FOR 
                      SELECT 
                      	id, no, first_name, last_name, mi, 
                      	dob, ssn, addr1, addr2, 
                      	city, state_code, zip_code, phone_num	
                      FROM SAMPLE_CUSTOMER
                      
                      Open cur 
                      
                      Fetch NEXT FROM cur INTO 
                      	@id, @no, @first_name, @last_name, @mi, 
                      	@dob, @ssn, @addr1, @addr2, 
                      	@city, @state_code, @zip_code, @phone_num	
                      While (@@FETCH_STATUS <> -1)  
                      BEGIN
                      IF (@@FETCH_STATUS <> -2)
                      
                      -- no:  Generate a random CustomerCode varchar(25), AAA####### 
                      SELECT @min = 1, @max = 26
                      SELECT @rand1 = ((@max + 1) - @min) * Rand() + @min 
                      SELECT @rand2 = ((@max + 1) - @min) * Rand() + @min 
                      SELECT @rand3 = ((@max + 1) - @min) * Rand() + @min 
                      
                      SELECT @min = 1, @max = 9999999
                      SELECT @rand = ((@max + 1) - @min) * Rand() + @min 
                      
                      SELECT @no = CHAR(64 + @rand1) + CHAR(64 + @rand2) + CHAR(64 + @rand3) + CAST(@rand as varchar(7))
                      
                      -- first_name
                      SELECT @first_name = first_name FROM SAMPLE_NAME WHERE id = ROUND(@max_name * rand(), 0)
                      
                      -- last_name
                      SELECT @last_name = last_name FROM SAMPLE_NAME WHERE id = ROUND(@max_name * rand(), 0)
                      
                      -- mi, Random Middle initial, 26% of column has a middle initial, 74% do not. 
                      SELECT @min = 1, @max = 100
                      SELECT @rand = ((@max + 1) - @min) * Rand() + @min 
                      
                      IF @rand > 26
                      	SET @mi = NULL
                      ELSE
                      	SET @mi = CHAR(64 + @rand)
                      
                      -- dob: Generate a random date 
                      SELECT @dob = dateadd(month, -1 * abs(convert(varbinary, newid()) % (90 * 12)), getdate()) 
                      
                      -- SSN - Random nine-digit number
                      SELECT @min = 1, @max = 999999999
                      Select @ssn = CAST(CAST(ROUND(((@max + 1) - @min) * Rand() + @min,0) as int)as varchar(9))
                      
                      -- addr1, House number - Random four-digit number + addr1
                      SELECT @min = 1, @max = 9999
                      Select @addr1 = CAST(ROUND(((@max + 1) - @min) * Rand() + @min,0) as varchar(4)) + ' ' + addr1 FROM SAMPLE_STREET WHERE id = ROUND(@max_address * rand(), 0)
                      
                      -- addr2:  5% of all addresses have a second address line, 95% do not. 
                      SELECT @min = 1, @max = 100
                      SELECT @rand = ((@max + 1) - @min) * Rand() + @min 
                      
                      IF @rand > 5
                      	SET @mi = NULL
                      ELSE
                      	SELECT @addr2 = addr2 FROM SAMPLE_STREET WHERE id = ROUND(@max_address * rand(), 0)
                      
                      -- city
                      SELECT @city = city FROM SAMPLE_CITY_STATE_ZIP  WHERE id = ROUND(@max_csz* rand(), 0)
                      
                      -- state
                      SELECT @state_code = state_code FROM SAMPLE_CITY_STATE_ZIP  WHERE id = ROUND(@max_csz* rand(), 0)
                      
                      -- zip
                      SELECT @zip_code= zip_code FROM SAMPLE_CITY_STATE_ZIP  WHERE id = ROUND(@max_csz* rand(), 0)
                      
                      
                      -- Phone - Random ten-digit number
                      SELECT @min = 1, @max = 9999999999
                      Select @phone_num = CAST(CAST(((@max + 1) - @min) * Rand() + @min as bigint) as varchar(15))
                      SELECT @phone_num = '(' + LEFT(@phone_num, 3) + ') ' + SUBSTRING(@phone_num, 4, 3) + '-' + RIGHT(@phone_num,4)
                      
                      UPDATE SAMPLE_CUSTOMER
                      SET
                      	no = @no, first_name = @first_name, last_name = @last_name, mi = @mi, 
                      	dob = @dob, ssn = @ssn, addr1 = @addr1, addr2 = @addr2, 
                      	city = @city, state_code = @state_code, zip_code = @zip_code, phone_num = @phone_num	
                      WHERE current of cur
                      
                      FETCH NEXT FROM cur INTO 	
                      	@id, @no, @first_name, @last_name, @mi, 
                      	@dob, @ssn, @addr1, @addr2, 
                      	@city, @state_code, @zip_code, @phone_num	
                      END
                      CLOSE cur
                      DEALLOCATE cur

Open in new window


Thank you for reading my article, please leave valuable feedback. If you liked this article and would like to see more, please click the 'Good Article' button. Thank You.  

If you have a question about this article, or would like to see something expanded as a future article, please post in this question.  

I look forward to hearing from you. -  Jim - ( LinkedIn ) ( Twitter )

sqlsat238.jpg
16
24,821 Views
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.

Comments (8)

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Author

Commented:
Nice.  I must find a way to be able to market stuff like this...
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Author

Commented:
If anyone is in the Twin Cities, Minnesota, USA area on October 12, 2013, I'll be presenting this at SQL Saturday #238.
Scott FellDeveloper & Coffee Roaster
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
Thanks Jim.  Great read!
Sharath SData Engineer
CERTIFIED EXPERT

Commented:
Thaks Jim. http://www.generatedata.com is really helpful.
Problem at line:

IF (@@FETCH_STATUS <> -2)

Msg 102, Level 15, State 1, Line 29
Incorrect syntax near ')'.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.