Solved

Create a stored procedure to insert 1 million entries random values

Posted on 2015-01-16
3
160 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Creating and Managing Databases with phpMyAdmin in cPanel.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

803 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