SP - Insert X number of rows - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SP - Insert X number of rows

Author

Topic

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-06-18 : 15:33:32

Can someone assist in forumulating a store procedure that will take a parameter "NumberOfTimes" and create that many entries into a table?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-18 : 15:51:00

[code]CREATE TABLE #a(i INT);
GO
CREATE PROCEDURE #b @i INT=0 AS
SET NOCOUNT ON;
WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<@i)
INSERT #a(i)
SELECT n FROM n WHERE @i>0;
GO
EXEC #b -- inserts nothing
EXEC #b 1 -- inserts 1 row
EXEC #b 2 -- inserts 2 rows[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 22:51:59

[code]
CREATE PROCEDURE #b
@i INT=0
AS
INSERT #a
SELECT number
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 1 AND @i
GO
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources