simple insert 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)

simple insert loop

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2009-10-22 : 09:38:42

Hi all,

I want to insert into a table these values:
970000000
970000001
970000002
970000003
970000004
970000005
........

like 10.000 like that.
what is the easiest way to achieve this?

Thanks a lot,

Mike

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-22 : 09:46:48

I would do it this way:


-- making a testtable
declare @table table(number int)

-- testsolution with 10 loops
declare @loop int
set @loop=0
while @loop < 10
begin
 insert @table 
 select 10000+@loop
 set @loop=@loop+1
end

-- show result
select * from @table

No, you're never too old to Yak'n'Roll if you're too young to die.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-22 : 09:50:19

create table #temp (number int)

declare @i int
set @i=0

while (@i<=10)
Begin

insert into #temp
select 970000000+@i

set @i=@i+1
End

select * from #temp

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-22 : 10:02:51

-- test solution with 10 recursions
;with number(n)
as
(
select 1 as n
union all
select n+1 from number where n<10
)

-- show result
select 970000000+n from number

Madhivanan

Failing to plan is Planning to fail

mike13
Posting Yak Master

219 Posts

Posted - 2009-10-22 : 15:21:08

thanks webfred that did the trick ;-)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 04:16:13

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

StevePD
Starting Member

2 Posts

Posted - 2009-10-23 : 22:29:16

Hey!
I would simply use a simple cursor, see example @
http://sites.google.com/site/sqlsimple/home/solutions

or for a more complex example, showing how to possibly integrate your cursor, check out:
"List all databases, table/views, columns, their data types and maximum lengths" on the same page, it uses the same logic.

StevePD

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-24 : 05:56:43

cursor - pah!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources