Creating test data with SQL

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

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top