Interesting Query Request - 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)

Interesting Query Request

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-04-15 : 12:25:19

I have a temp table that contains the following columns, "symbol" and "items". I want to insert those records into another table. Sound simple enough except... I want to insert the same record over and over again depending on the number defined in the items column.

For instance, the data contained in the temp table below looks like:

SYMBOL ITEMS
98G 1
AB 50
CD 100

In this case, I want to insert 98G one time into a temp table, AB would get inserted 50 times, CD would get inserted 100 times, and so on. There are obviously more columns not being displayed.

Thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-04-15 : 13:02:16

I'd suggest writing a table value function that accepts the values in your table and returns the appropriate number of entries and then using that function in your insert/select statement:[CODE]create function RepString (
@String varchar(10),
@Count int
)
returns @RepTbl table (
String varchar(10)
)
as
begin
while (@Count > 0)
begin
insert into @RepTbl(String)
values (@String)

set @Count -= 1
end

return
end
go

---------------------------------------------------------
declare @tbl table (
symbol varchar(10),
items int
)

insert into @tbl
select '98G', 1 union all
select 'AB', 50 union all
select 'CD', 100

select b.String
from @tbl a
cross apply RepString(symbol, items) b[/CODE]

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-04-15 : 13:12:06

Thanks for the suggestion, I will give it a try.

Did you really mean to say "set @Count -= 1"?

Kristen
Test

22859 Posts

Posted - 2010-04-15 : 13:37:53

"Did you really mean to say "set @Count -= 1"?"

I think that "decrement by one" feature was introduced in SQL 2008

If you have SQL 2005, or earlier, the equivalent would be:


set @Count = @Count - 1

which is just SO much extra typing that everyone should upgrade IMMEDIATELY!

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-04-15 : 14:42:10

Ha ha, I know... We are still on SS 2005.

Thanks for the info.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-16 : 10:25:42

<<
which is just SO much extra typing that everyone should upgrade IMMEDIATELY!
>>

That is derived from C++
But it loses readability

Madhivanan

Failing to plan is Planning to fail

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-04-16 : 17:52:35

In Re: which is just SO much extra typing that everyone should upgrade IMMEDIATELY!

Funniest thing that I've seen all day. Thanks for the tickle.

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-18 : 06:52:16

Create a number table

INSERT INTO @tbl
SELECT SYMBOL, ITEMS
FROM NumberTable nt LEFT JOIN (SELECT SYMBOL, ITEMS)counting, SYMBOL, ITEMS FROM @source)s ON nt.numbers <= s.ITEMS
WHERE SYMBOL IS NOT NULL

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources