Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Create a stored procedure to insert 1 million entries random values

Posted on 2015-01-16
3
Medium Priority
?
312 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 26

Accepted Solution

by:
Tomas Helgi Johannsson earned 1000 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 1000 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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month12 days, 18 hours left to enroll

971 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