Avoiding loops - 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)

Avoiding loops

Author

Topic

rajmub2006
Starting Member

2 Posts

Posted - 2009-09-29 : 10:35:39

Can someone help me with inserting records in a table without using loops?

I have table with the following structures:

Create table1(

Code varchar(20),

Count int)

Create table2(

Code varchar(20),

Data varchar(20))

What I want to do is insert number records into table2 that is driven by value contained in the count field in table1.

For e.g.

If table1 has the following data:

Code Count

A1 3

then the following data needs to be inserted into table2

Code Data

A1 'aa'

A1 'aa'

A1 'aa'

I can read the count field value from table1 and then create a loop to insert that many rows into table2.

But I want to know if there is any way to do this without using loops.

Thanks in advance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-29 : 10:56:07

Sounds like what you are talking about is generating new rows, then number of which is based on a [count] column. To do this you can JOIN to a table of numbers (usually INTs from 0 to whatever the largest [count] value is. People use either a numbers function, a permanent numbers table, or even just a derived table or sub-query which results in a series of INTs. In this case I'll just use an existing table in the Master database as this table:


use tempdb
go
Create table table1
       (Code varchar(20),
       [Count] int)
go
Create table table2
       (Code varchar(20),
       Data varchar(20))
go
insert table1 values('A1',3)

insert table2
select t.code
       ,'aa'
from   table1 t
inner join master..spt_values n
       on n.type = 'P'
       and n.number < t.[count]

select * from table2
go

drop table table1
drop table table2

OUTPUT:
Code                 Data
-------------------- --------------------
A1                   aa
A1                   aa
A1                   aa

Be One with the Optimizer
TG

rajmub2006
Starting Member

2 Posts

Posted - 2009-09-29 : 11:26:03

Thanks for your reply.

But I don't think I will be able to query the table in master database in prod.

Is there any function that will give me table with values from 1-99?
Or can give me the derived query for doing this?

Thanks

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-09-29 : 11:55:09

used recursive cte from here to generate no.s:
http://www.simple-talk.com/sql/t-sql-programming/sql-server-2005-common-table-expressions/

declare @table1 table(
Code varchar(20),
[Count] int
)

declare @table2 table
(
Code varchar(20),
Data varchar(20)
)

INSERT INTO @table1
SELECT 'A',3;

with MyCTE
as
(
select a.Code, i = 1 from @table1 As A
union all
select a.Code,i = i + 1 from MyCTE , @table1 As A where i < (SELECT [COUNT] FROM @table1)
)

INSERT INTO @TABLE2
Select Code,'aa' From MyCTE

Select * from @TABLE2

--------------------
Rock n Roll with SQL

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-29 : 13:02:14

quote:
Originally posted by rajmub2006

Thanks for your reply.

But I don't think I will be able to query the table in master database in prod.

Is there any function that will give me table with values from 1-99?
Or can give me the derived query for doing this?

Thanks

Check this out for the ultimate numbers function :)
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources