select statement to display name 1000 times

[arun_pk]

pls help me out to display a string for example 'abcd' 1000 times using select statement in SQL

[Niladri_Biswas]

a)

select REPLICATE ('abcd', 1000)

b)

PRINT 'abcd'
GO 10000

c) If using sql server 2005+

with cte as
( select 1 as rn,'abcd' as data
union all
select rn+1, 'abcd' as data from cte where rn<1000)

select data from cte option (maxrecursion 0)

d)

DECLARE @x XML
SELECT @x = REPLICATE ('<x>abcd</x>', 1000)
SELECT i.value('.', 'VARCHAR(MAX)') ReplacedStrings
FROM @x.nodes('//x') x(i)

:)

Niladri Biswas

modified on Tuesday, December 1, 2009 6:10 AM

[arun_pk]

thanks for the response but this gives me an output in this manner abcdabcdabcdabcd.................. i m looking for abcd in 1000 records likes this abcd abcd abcd abcd . . . . . . . .

[Niladri_Biswas]

Follow the third approach i.e.

recursive cte

:)

Niladri Biswas

[Blue_Boy]

One of the ways is:

create table #tblname (
col1 varchar(200)
)

declare @i as int
set @i=0
while @i<=1000
begin
set @i=@i+1
insert into #tblname values ('abc')
end

select * from #tblname

drop table #tblname

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[arun_pk]

thanks its coming how will i be able to insert that 'abcd' 1000 times in my insert given below insert into [dbo].[Test](NAME)

[arun_pk]

thanks for the response i m looking for doing with out looping

[Niladri_Biswas]

A) With recursive cte approach

declare @t table(name varchar(50))
;with cte as
( select 1 as rn,'abcd' as data
union all
select rn+1, 'abcd' as data from cte where rn<1000)

insert into @t
select data from cte option (maxrecursion 0)

select * from @t

B) Xquery approach

declare @t table(name varchar(50))
DECLARE @x XML
SELECT @x = REPLICATE ('<x>abcd</x>', 1000)
insert into @t
SELECT i.value('.', 'VARCHAR(MAX)') ReplacedStrings
FROM @x.nodes('//x') x(i)

select * from @t

:)

Niladri Biswas

[Ashfield]

use the same method as you were told previously, a cartesian join, and limit it

select top 1000 'a'
from dbo.sysobjects as so1, dbo.sysobjects as so2

and buy a book on SQL.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[arun_pk]

thnks a looooooot

[arun_pk]

thanks for ur advice but u are failed to answer my q u buy SQL basics

[Ashfield]

arun_pk wrote:

but u are failed to answer my q

In what respect?

select top 1000 'a' from dbo.sysobjects as so1, dbo.sysobjects as so2

will do as your (vague) question asks.

arun_pk wrote:

u buy SQL basics

Why? Its not me asking basic questions about sql, its you that doesn't understand what you are being told will work.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Lost User]

arun_pk wrote:

but u are failed to answer my q

Ashfield's query works perfectly for your requirement.

arun_pk wrote:

u buy SQL basics

I just saw you being spoon fed by Niladri_Biswas and you are advising others to learn SQL basics ? Funny.

[Mycroft Holmes]

Cute - what if sysobjects less than 100 records, add another cross join, and another :laugh: !

Never underestimate the power of human stupidity RAH

[Ashfield]

Mycroft Holmes wrote:

what if sysobjects less than 100 records, add another cross join

Yep. Mind you, by default sysobjects has about 40 rows, and 40 * 40 is 1600 so he should be safe. And at the end of the day, he now has enough info to work it out for himself :)

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Mycroft Holmes]

Ashfield wrote:

he now has enough info to work it out for himself

Not with the spoon feeding Niladri had to do, I doubt he has the nous to research cross join!

Never underestimate the power of human stupidity RAH