Creating test data with SQL

Learn how to create data that you can work with when designing, testing, or tweaking your database application.

This tip, from Judith S. Bowman's book Practical SQL: The Sequel (Addison-Wesley, 2001), shows you how to create data that you can work with when designing, testing, or tweaking your database application.

For example, let's say you need more rows in a table called "customer". If you have a unique index you can't just insert more copies of the existing rows. Thus, you'll need to modify the customer numbers so that each row will have a different one. Here's where CONVERT functions some in handy. You change the character datatype to a numeric one and add some value--in this case, 11:

SELECT custnum, cast (custnum +11 as integer)
FROM customer
ORDER BY custnum

Some resultant rows:

custnum		customer.custnum+11
=======		===================
111222222	111222233
111223333	111223344
111333333	111333344
223456789	223456800
923457789	923457800

Now, in order to use the changed customer number, you have to convert it back to a CHAR(9):

INSERT INTO customer (custnum, 
	lname, address, city, state, postcode,
	areacode, phone, status)
SELECT cast ( cast (custnum +11 as integer) as char(9) ),
	lname, address, city, state, postcode,
	areacode, phone, '4'
FROM customer

You can continue inserting the rows back into the table in this way until you have the table size you want. You may need to vary the number you add to custnum.

INSERT INTO customer (custnum, 
	lname, address, city, state, postcode,
	areacode, phone, status)
SELECT cast ( cast (custnum +21 as integer) as char(9) ),
	lname, address, city, state, postcode,
	areacode, phone, '4'
FROM customer

INSERT INTO customer (custnum, 
	lname, address, city, state, postcode,
	areacode, phone, status)
SELECT cast ( cast (custnum +30 as integer) as char(9) ),
	lname, address, city, state, postcode,
	areacode, phone, '4'
FROM customer

INSERT INTO customer (custnum, 
	lname, address, city, state, postcode,
	areacode, phone, status)
SELECT cast ( cast (custnum +35 as integer) as char(9) ),
	lname, address, city, state, postcode,
	areacode, phone, '4'
FROM customer

To remove extra rows, just look for those that have a 4 in the status column.

DELETE FROM customer
WHERE status = 4

About the Author

Judith S. Bowman is author of Practical SQL: The Sequel.

For More Information


This was first published in January 2001

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close