I want populate with dummy values a table.
I want produce a random produced value "RANDOM_VALUE" for every row produced.
Random value should be a string and it is range might be only 'A' or 'B'.
The script should have the ability to list a number of row with value A or B at random produced
Here a good example of how should work:
ROW RANDOM_VALUE 1 A 2 B 3 B 4 A ... ...
Any ideas how to get it done? Help I'm very new in SQL thanks!
Rand() is just examined once per column so would be the same for those rows to obtain for this you should use
NewId() as below.
SELECT CHAR(65+ABS(CHECKSUM(NEWID()))%2), RestOfCols FROM YourTable
If you want the output exactly according to your question just join onto a large enough table. e.g.
WITH cte AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW, CHAR(65+ABS(CHECKSUM(NEWID()))%2) AS RANDOM_VALUE FROM sys.objects ) INSERT INTO DummyTable SELECT ROW,RANDOM_VALUE FROM cte WHERE ROW<= 4
for producing test data, you will probably find it valuable to possess deterministic data. Quite simply, any time you generate data, it is the same. This way it's simpler to breed bugs.
With this, you should use
hashbytes() on the deterministic seed. Quite simply:
create function dbo.fn_RandishInt(@seed nvarchar(max), @min int, @max int) returns int as begin declare @crc bigint declare @p float set @crc = cast(cast(hashbytes('md5', @seed) as int) as bigint) & 0xffffffff set @p = cast(@crc as float) / 4294967296 return round(((@max - @min) * @p) + @min, 0) end go SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS s, char(dbo.fn_RandishInt(ROW_NUMBER() OVER (ORDER BY (SELECT 0)), 65, 66)) AS t FROM sys.objects
this can always yield exactly the same, random-searching, results.
Make your own Random number function such as this, that can be used instead of Rand().
CREATE VIEW dbo.RandomNumberView AS SELECT Rand() AS RandomNumber GO CREATE FUNCTION dbo.RandomNumber() RETURNS float AS BEGIN RETURN (SELECT RandomNumber FROM dbo.RandomNumberView) END GO