Solved

Create a stored procedure to insert 1 million entries random values

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

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

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…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 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

17 Experts available now in Live!

Get 1:1 Help Now