|
|
I often need to fill up tables with random data to test data conversion scripts, foreign keys, indexes, etc. Here’s my “Go To” script for coming up with random data. I usually base it off this and change the name of the table to insert data into, but this has the things I need to remember, like an easy way to loop through code, NEWID(), RAND(), RND(), etc.
Declare @TestTable Table
(ID uniqueidentifier,
Num int,
InsertDate datetime)
Declare @icount int,
@TopNum int,
@Repeat int
Set @icount = 1
-------------------------
---Set the ranges here---
-------------------------
Set @TopNum = 10 --Highest number you want - note this will be 0 - 10
Set @Repeat = 1000 --number of times to repeat
-------------------------
-------------------------
While @icount < @Repeat
begin
insert @TestTable
Select NewID(),
ROUND((RAND() * (@TopNum)),0),
GETDATE()
Set @icount = @icount + 1
End
Select Num, count(Num) from @TestTable group by Num
order by Num
One of my goals for 2010 is to get more involved in the SQL server community. A great way to do this is to use twitter. I’ve been using twitter since January of 2009 – you can follow me here. I’ve had my high usage times (where I check it and post to it all the time) and I’ve had my slower times (where I haven’t opened up tweetdeck in a month). Anywho – I’ve been using it now quite a bit and here’s my SQL lists.
If you want to learn the basics of Twitter, here’s the link for BrentOs “Twitter101”. I do suggest you read it and follow him as he’s pretty sharp.
Other Twitter people I like to read are….never mind, I was going to do a list, but there’s so many possibilities with this list – there’s something out there for everybody…..
- From SQL Guru’s
- to local sports reporters
- to celebrities
- to athletes
- to old college friends
- to old co-workers
- to cousins
- to news
- etc…
Here’s a link to follow me on twitter:
Here’s a simple script that I use often – a quick hitter to find table row counts, that doesn’t use the Select Count(*). It uses the sysindexes table thus the reason it’s called “fairly accurate”.
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC
Here’s a script I created to determine when I was going to be at specific miles for a marathon. It’ll give you your miles per hour and your minutes per mile. Then it’ll calculate at what time you will be at different mile marks.
Set nocount on
Declare @FTime datetime,
@Dist float,
@RStartTime datetime,
@BDown float
-----------------------------------
----Change these for you event-----
-----------------------------------
Set @FTime = '3:10:00'
Set @Dist = 26.4
Set @RStartTime = '6/19/10 7:30 AM'
Set @BDown = 1 --this is the breakdown desired
-----------------------------------
-----------------------------------
-----------------------------------
Declare @Htime int,
@Mtime int,
@STime int,
@TSec int,
@SPM float,
@iCounter float
Declare @TheList table (Mile float,
RTime datetime,
CrossingTime time)
Set @Htime = Datepart(hh, @FTime)
Set @Mtime = Datepart(mi, @FTime)
Set @Stime = Datepart(ss, @FTime)
Set @TSec = (@Htime*60*60) + (@Mtime*60) + @Stime
Set @SPM = @TSec/@Dist
Set @iCounter = @BDown
While @icounter < @Dist
begin
insert @TheList
Select @iCounter,
Dateadd(ss, @iCounter*@SPM, 0),
DateAdd(ss, @iCounter*@SPM, @RStartTime)
Set @iCounter = @iCounter + @BDown
End
---Insert Last one if not an even mile
IF Convert(int, 1000*@Dist) % Convert(int, 1000*@iCounter) <> 0
Begin
insert @TheList values(@Dist, Dateadd(ss, @Dist*@SPM, 0), DateAdd(ss, @Dist*@SPM, @RStartTime))
End
Select Convert(varchar(20), Dateadd(ss, @SPM, 0), 8 ) as MPM,
Round(3600/@SPM, 3) as MPH
Select Mile,
Convert(varchar(20), RTime, 8 ) as Time,
Convert(varchar(20), CrossingTime, 100) as 'SchedTime'
from @TheList
/*
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Creates a header for scripts --
-- Pastes the results of this into a script --
-- --
-- Have the results returned to text --
-- Change the maximun characters return to 700 --
-- --
-- Created by Andy Lohn on 12/15/09 --
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
Set nocount on
Declare @Comments1 varchar(74),
@Comments2 varchar(74),
@Comments3 varchar(74),
@Comments4 varchar(74),
@Comments5 varchar(74),
@Comments6 varchar(74),
@Comments7 varchar(74)
Select @Comments1 = 'This creates a simple header for scripts'
Select @Comments2 = '<<<fill in comments/notes here>>>>'
Select @Comments3 = '<<<fill in comments/notes here>>>>'
Select @Comments4 = '<<<fill in comments/notes here>>>>'
Select @Comments5 = '<<<fill in comments/notes here>>>>'
Select @Comments6 = ''
Select @Comments7 = 'Created by Andy Lohn of Lohn Solutions on ' + Convert(varchar(20), Getdate(), 1)
Select '/*' +
Char(13) + Char(10) +
Replicate('-', 80) +
Char(13) + Char(10) +
'-- ' + @Comments1 + SPACE(74 - Len(@Comments1)) + '--' +
Char(13) + Char(10) +
'-- ' + @Comments2 + SPACE(74 - Len(@Comments2)) + '--' +
Char(13) + Char(10) +
'-- ' + @Comments3 + SPACE(74 - Len(@Comments3)) + '--' +
Char(13) + Char(10) +
'-- ' + @Comments4 + SPACE(74 - Len(@Comments4)) + '--' +
Char(13) + Char(10) +
'-- ' + @Comments5 + SPACE(74 - Len(@Comments5)) + '--' +
Char(13) + Char(10) +
'-- ' + @Comments6 + SPACE(74 - Len(@Comments6)) + '--' +
Char(13) + Char(10) +
'-- ' + @Comments7 + SPACE(74 - Len(@Comments7)) + '--' +
Char(13) + Char(10) +
Replicate('-', 80) +
Char(13) + Char(10) +
'*/'
|
|
