Solved

Create a stored procedure to insert 1 million entries random values

Posted on 2015-01-16
3
124 Views
Last Modified: 2016-06-18
I need to Create a stored procedure to insert 1 million entries into my table tbl_student and execute it.

Columns:

int_enrollment_no(int),
pk_int_roll_no(int primarykey),
vchr_name(varchar),
vchr_city(varchar),
vchr_mobileno(varchar),
dat_dob(date)

Open in new window


These are the attributes, but the entries shouldn't be equal. I must insert random values. I need code for this.
0
Comment
Question by:jenthuz ojs
3 Comments
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 250 total points
ID: 40553171
Hi!

Take a look at this solution
http://kedar.nitty-witty.com/blog/generate-random-test-data-for-mysql-using-routines

You can easily modify this to your needs.

Regards,
     Tomas Helgi
0
 
LVL 8

Assisted Solution

by:Ganapathi
Ganapathi earned 250 total points
ID: 40553178
Use this as an example:
CREATE TABLE #TEST
(
	int_enrollment_no int,
	pk_int_roll_no int,
	vchr_name varchar(255),
	vchr_city varchar(255),
	vchr_mobileno varchar(10),
	dat_dob date
)

CREATE PROCEDURE INSERT_PROC
AS
BEGIN
	DECLARE @lnMIN INT,
			@lnMAX INT
	SELECT @lnMIN = 0		
			
	WHILE (@lnMIN < 100)
	BEGIN
		INSERT INTO #TEST
		SELECT
			CEILING(RAND() * 1000000),
			CEILING(RAND() * 10000000),
			CONVERT(varchar(255), NEWID()),
			CONVERT(varchar(255), NEWID()),
			CONVERT(VARCHAR(10),CEILING(RAND() * 1000000)),
			dateadd(second,(rand()*60+1),DATEADD(minute,(rand()*60+1) ,DATEADD(day,(rand()*365+1),DATEADD(year,-1,getdate()))) )
		SELECT @lnMIN = @lnMIN + 1
	END
END
GO
             
SELECT * FROM #TEST

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now