June 9, 2008 at 11:25 am
Hello,
I am brand new to SQL Server with two decades of Oracle background. Therefore I may be looking for Oracle solutions in the Microsoft world and I apologize for that in advance.
I would like to test scenarios with relatively big tables. How do I write a script to populate the table? This is what I would do in PL/SQL (Oracle's procedural SQL language):
[font="Courier New"]declare
i number;
begin
loop
insert into t values(i);
i := i + 1;
exit when i=10001;
end loop;
end;
/[/font]
Is there any similar (or not) easy solution in SQL Server?
If yes, where is the appropriate procedural language document that I could read?
Thanks very much in advance!
Gabor
June 9, 2008 at 12:03 pm
Well I would do it like this on SQL Server:
insert
into t
select TOP (100000) Row_number() Over (order by c1.id, c1.name, c2.id, c2.name)
From master..syscolumns c1
cross join master..syscolumns c2
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2008 at 1:22 pm
Here is the solution similiar to what you want to write...
Declare @i as int
set @i = 0
While @i < 100000
Begin
insert into t values (@i)
Set @i = @i + 1
End
Books online would be a good place to start for you.
June 9, 2008 at 2:09 pm
Take a look at this page and the discussion. It's got the scoop on generating massive amounts of data for testing, and a bunch of related subjects:
http://www.sqlservercentral.com/articles/TSQL/62867/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2008 at 2:10 pm
Hello,
Yes, that works a LOT faster!
Thanks!!!
Gabor
June 9, 2008 at 2:21 pm
Well, I'm glad one of us helped. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2008 at 2:29 pm
Thanks! Very interesting read! Learnt a lot from it!
Gabor
GSquared (6/9/2008)
http://www.sqlservercentral.com/articles/TSQL/62867/%5B/quote%5D
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply