Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query

Author

Topic

sqldev05
Starting Member

9 Posts

Posted - 2008-04-03 : 19:13:13

Hi,

I have a table with few columns..Can anyone tell me the easiest way to insert around 500000 records into a table.. I just want to fill table with junk data.for testing purpose.....I want to insert data into the table thrugh Stored Procedure only..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-03 : 20:02:00

Cross join to this user defined function (by Michael Valentine Jones)
F_TABLE_NUMBER_RANGE

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=F_TABLE_NUMBER_RANGE

Be One with the Optimizer
TG

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-04 : 02:18:50

The following code creates a Customer table and populates random values.. U can as well use such a method!

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CUSTOMER'
AND TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='dbo')

CREATE TABLE [dbo].[Customer](
[Age] [int] NULL,
[Salary] [int] NULL,
[Experience] [int] NULL,
[Nature] [CHAR](1) NULL
)

DECLARE @AGE INT
DECLARE @SALARY INT
DECLARE @EXPERIENCE INT
DECLARE @NATURE CHAR(1)
DECLARE @COUNT INT

SET @COUNT=1

WHILE @COUNT<=100
BEGIN

SET @AGE=RAND()*100
SET @SALARY=RAND()*10000
SET @EXPERIENCE=RAND()*10
SET @COUNT=@COUNT+1

IF ROUND(RAND(),0)=1
SET @NATURE='G'
ELSE
SET @NATURE='B'

INSERT INTO CUSTOMER VALUES(@AGE,@SALARY,@EXPERIENCE,@NATURE)

END

Prakash.P
The secret to creativity is knowing how to hide your sources!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-04 : 02:46:37

quote:
Originally posted by pravin14u

The following code creates a Customer table and populates random values.. U can as well use such a method!

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CUSTOMER'
AND TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='dbo')

CREATE TABLE [dbo].[Customer](
[Age] [int] NULL,
[Salary] [int] NULL,
[Experience] [int] NULL,
[Nature] [CHAR](1) NULL
)

DECLARE @AGE INT
DECLARE @SALARY INT
DECLARE @EXPERIENCE INT
DECLARE @NATURE CHAR(1)
DECLARE @COUNT INT

SET @COUNT=1

WHILE @COUNT<=100
BEGIN

SET @AGE=RAND()*100
SET @SALARY=RAND()*10000
SET @EXPERIENCE=RAND()*10
SET @COUNT=@COUNT+1

IF ROUND(RAND(),0)=1
SET @NATURE='G'
ELSE
SET @NATURE='B'

INSERT INTO CUSTOMER VALUES(@AGE,@SALARY,@EXPERIENCE,@NATURE)

END

Prakash.P
The secret to creativity is knowing how to hide your sources!

Another method


select 
	abs(checksum(newid()))%100,abs(checksum(newid()))%10000,abs(checksum(newid()))%10,
	case when abs(checksum(newid()))%2=1 then 'G' else 'B' end 
from 
	master..spt_values where type='p' and number between 1 and 200

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources