<

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

Published on
32,624 Points
16,024 Views
16 Endorsements
Last Modified:
Awarded
Jim Horn
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.
(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
Comment
Author:Jim Horn
7 Comments
LVL 61

Expert Comment

by:mbizup
Jim,

Great stuff!

Now if we could just get some of the folks who upload samples containing real data to forums to read this article...
0

Expert Comment

by:Rayne
This is extremely useful for string stuff. 10 Stars !!
thanks JimHorn
0
LVL 50

Expert Comment

by:PortletPaul
Excellent stuff Jim, thanks. http://www.generatedata.com looks really handy also.

btw: check out http://www.fakenamegenerator.com/

Cheers, Paul
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LVL 66

Author Comment

by:Jim Horn
Nice.  I must find a way to be able to market stuff like this...
0
LVL 66

Author Comment

by:Jim Horn
If anyone is in the Twin Cities, Minnesota, USA area on October 12, 2013, I'll be presenting this at SQL Saturday #238.
0
LVL 55

Expert Comment

by:Scott Fell, EE MVE
Thanks Jim.  Great read!
0
LVL 41

Expert Comment

by:Sharath
Thaks Jim. http://www.generatedata.com is really helpful.
0

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month