As im playing around with SSRS caching and snapshots at the moment I needed to have some data coming into the table im reporting off that’s a little bit dynamic.  The brain of the work comes from SQLFools random number generator. (Read the comments for some other possible ways to get random numbers.)

http://sqlfool.com/2009/06/random-number-generator-in-tsql/

I put the random number into a CASE statement and just throw the whole script into an agent job that runs every min to get some random data 🙂

Create a table to use

CREATE TABLE cacheData (
[id] INT IDENTITY (1,1),
[name] VARCHAR(20),
[date] DATETIME DEFAULT GETDATE()
)

And below is the code to populate it with random data:

-- Random num generator from: http://sqlfool.com/2009/06/random-number-generator-in-tsql/
DECLARE @maxRandomValue TINYINT = 3 ,
@minRandomValue TINYINT = 1;
DECLARE @random TINYINT
SET @random = CAST(( ( @maxRandomValue + 1 ) - @minRandomValue ) * RAND()
+ @minRandomValue AS TINYINT)
-- Test @random
-- PRINT @random
-- INSERT STATEMENT based off @random
INSERT INTO cachedata
( name
)
VALUES ( CASE @random
WHEN 1 THEN 'Timmy'
WHEN 2 THEN 'Tommy'
WHEN 3 THEN 'Johnny'
END
)
-- View Results
-- SELECT * FROM cachedata

 

sqlserver_logo

Advertisements