Count for a Loop - 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)

Count for a Loop

Author

Topic

jerryleguer
Starting Member

2 Posts

Posted - 2010-12-03 : 12:55:19

I have table1

id (identity)
Description

I want to create the records for table1 based on a count of another table/

Select count() from table2.

How do I insert the amount of records based on the count result.

Example: If the count result is 5 I would like to insert 5 new rows into table1.

Thanks

bobmcclellan
Starting Member

46 Posts

Posted - 2010-12-03 : 13:15:35

one way....
declare @RowsToInsert int, @Cnt int
set @cnt = 0
set @RowsToInsert = 5 -- actually will be (select count(*) from table 2

create
table #t ( col1 char(10), id int identity )

while @Cnt < @RowsToInsert
begin
insert into #t ( col1 ) values ('something')
set @cnt = @cnt +1
end

select *
from #t

drop table #t

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-03 : 13:25:04

Another option is to use a Number/Tally table. Here is one way with an inline tally table:

DECLARE @RowsToInsert INT = 5
DECLARE @Table TABLE (ID INT IDENTITY(1,1), Val INT)

;WITH 
Tens (N)     AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                 SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9), 
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3), 
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2), 
Tally (N)    AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

INSERT @Table (Val)
SELECT N
FROM Tally
WHERE N <= @RowsToInsert

SELECT *
FROM @Table

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-12-03 : 13:51:50

insert into Table1 (Description)
select 'Do not know what to put into this column but hopefully you do.'
from Table2

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources