insert dummy values using WHILE loop in SQL SERVER

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

SQL Server 2008 Forums

Transact-SQL (2008)

insert dummy values using WHILE loop in SQL SERVER

Author

Topic

raaj
Posting Yak Master

129 Posts

Posted - 2012-05-14 : 22:07:40

Hi Guys,
I have a table named PERSON.
I got this requirement that this table needs to be filled with 300 dummy values. This table has columns 'Person_ID' (which is a Identity (1,1)) column and 'Name' column.
I want the dummy values in this format :
Person_ID Name
1 Person1
2 Person2
3 Person3
4 Person4
5 Person5
..................
..................upto Person300.

Can anyone suggest How can I write a simple query to get the above result set?

I tried the below WHILE Loop :

DECLARE @intLimit AS INT = 300
DECLARE @intCounter AS INT = 1

WHILE @intCounter <= @intLimit
BEGIN
INSERT INTO Person VALUES ('Person1')
SET @intCounter = @intCounter + 1
END

SELECT * FROM Person

But When I run the above query, I am getting the following resultset :
Person_ID Name
1 Person1
2 Person1
3 Person1
4 Person1
5 Person1
.........................
.........................

So, I want the Name column to have rows Person1,Person2,Person3.....Person300.

Any ideas How to do achieve this?

Thanks,
Raaj.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-14 : 22:53:33

[code]
;WITH NumberTable (N)
AS
(
SELECT 1
UNION ALL
SELECT N+1
FROM NumberTable
WHERE N+1 <=300
)

INSERT PERSON (Name)
SELECT 'Person' + CAST(N AS varchar(3))
FROM NumberTable
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-15 : 05:35:04

quote:
Originally posted by visakh16

;WITH NumberTable (N)
AS
(
SELECT 1
UNION ALL
SELECT N+1
FROM NumberTable
WHERE N+1 <=300
)

INSERT PERSON (Name)
SELECT 'Person' + CAST(N AS varchar(3))
FROM NumberTable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

+1.
As Visakh showed it to you, it is best done with a NUMBERS/TALLY TABLE.
To know more about TALLY TABLE, check out the following link:

[url]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

N 28° 33' 11.93148"
E 77° 14' 33.66384"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-15 : 07:08:54

quote:
Originally posted by visakh16

;WITH NumberTable (N)
AS
(
SELECT 1
UNION ALL
SELECT N+1
FROM NumberTable
WHERE N+1 <=300
)

INSERT PERSON (Name)
SELECT 'Person' + CAST(N AS varchar(3))
FROM NumberTable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

An addition to Visakh's query (in RED ).

.....
SELECT  'Person' + CAST(N AS varchar(3))
FROM NumberTable
OPTION (MAXRECURSION 300);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources