-- 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))
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.
-- 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')
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')
… 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')
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
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.
-- 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
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)
The Rand() function creates a random number between 0 to 1 with 15 decimal places.
-- 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))
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)
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)
Date of Birth: Create a random date..
-- dob: Generate a random date
SELECT @dob = dateadd(month, -1 * abs(convert(varbinary, newid()) % (90 * 12)), getdate())
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))
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)
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)
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)
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)
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
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
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.
Comments (8)
Author
Commented:Author
Commented:Commented:
Commented:
Commented:
IF (@@FETCH_STATUS <> -2)
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near ')'.
View More