Random Populate a Table
Is quite a joke, but useful, I guess, and, of course, improvable. It need to use two random number generator (inclued) published some weeks ago here.
Some fieldtype needs to be generating-improved (e.g. images) ... I wait suggestions and improvements
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fx_convertVarcharHexToDec]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fx_convertVarcharHexToDec]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fx_generateRandomNumber]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fx_generateRandomNumber]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[filltable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[filltable]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fx_convertVarcharHexToDec
(@varHex varchar(8))
RETURNS int
AS
BEGIN
/*
Used to convert Hexidecimal values to Int
author: henk-nospam-@hatchlab.nl
2004-01-09
*/
declare @val_int int
declare @val_hex varchar(10)
set @val_hex = @varHex
--convert hex-varchar to integer.
set @val_int =
((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),1,1),'0123456789ABCDEF')-1)*power(16,7))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),2,1),'0123456789ABCDEF')-1)*power(16,6))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),3,1),'0123456789ABCDEF')-1)*power(16,5))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),4,1),'0123456789ABCDEF')-1)*power(16,4))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),5,1),'0123456789ABCDEF')-1)*power(16,3))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),6,1),'0123456789ABCDEF')-1)*power(16,2))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),7,1),'0123456789ABCDEF')-1)*power(16,1))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),8,1),'0123456789ABCDEF')-1)*power(16,0))
--display.
return @val_int
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fx_generateRandomNumber(
@guid as uniqueidentifier,
@intMin int = 0,
@intMax int = 10 )
RETURNS int
AS
/*
Used to generate random ints in the range of [min, ..,max].
The @guid param should always be called with the newid() as value, this will create better series of random numbers
author: henk-nospam-@hatchlab.nl
2004-01-09
UPDATED: 2004-07-20, Artur Szlejter; Fiexed: When lowerbound higher then 0 was choosen it generated numbers between [0-lowerbound].
*/
BEGIN
declare @tmp1 as int
declare @tmp2 as numeric(10,3)
declare @tmp3 as numeric(10,3)
set @tmp1 = dbo.fx_convertVarcharHexToDec('0x' + right(cast
(@guid as varchar(64)), 2))
set @tmp2 = (@intMax - @intMin) / cast(255 as numeric(10,3))
--filter factor
set @tmp3 = (@tmp1 * @tmp2) + @intMin
return cast(round(@tmp3, 0) as int)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.filltable (@TableName Varchar(100)='', @records int = 100) as
print 'Filltable procedure'
/*
needs the number generator functions called fx_ConverVarCharToDec
and fx_generateRandomNumber
*/
Declare @ColumnName Varchar(8000)
declare @TableID Int
DECLARE @FIELD_NAME VARCHAR(254)
DECLARE @FIELD_TYPE VARCHAR(30)
DECLARE @FIELD_SIZE INT
DECLARE @FIELD_DEFAULT VARCHAR(254)
DECLARE @AUTOVAL VARCHAR(254)
declare @temp varchar(254)
DECLARE @counter smallint
declare @leftsql varchar(4096)
declare @rightsql varchar(4096)
declare @i int
declare @recno int
declare @value varchar(2048)
declare @dummy varchar(5)
If Len(@TableName) = 0
Begin
print 'No tablename supplied'
end
else
begin
If Not Exists(Select 1 From SysObjects Where ID = Object_ID(@TableName) And xType = 'U')
Begin
Print 'The Passed parameter [' + @TableName + '] is not an User Table'
Return 0
End
Else
Begin
SELECT
TOP 100 PERCENT obj.name AS table_name,
cols.name AS field_name,
type.name AS field_type,
cols.length AS field_size,
props.[value] AS field_description,
cols.isnullable AS field_nullable,
type.tdefault AS field_default,
autoval, -- if autoinc field
space(cols.length) as GeneratedString
into #temp
FROM
dbo.sysobjects obj
INNER JOIN
dbo.syscolumns cols ON obj.id = cols.id
LEFT OUTER JOIN
dbo.sysproperties props ON cols.id = props.id
AND cols.colid = props.smallid
LEFT OUTER JOIN
dbo.systypes type ON cols.xtype = type.xusertype
WHERE (obj.type = 'U') and (obj.name = @tablename)
ORDER BYtable_name
-- now I can browse the generated table and create fake data
-- to put later inside the final table
select @recno = 1
while @recno < @records+1
begin
declare Browser_cursor cursor for
select FIELD_NAME, FIELD_TYPE, FIELD_SIZE, FIELD_DEFAULT, AUTOVAL from #temp
open Browser_cursor
fetch next from Browser_cursor
INTO @FIELD_NAME, @FIELD_TYPE, @FIELD_SIZE, @FIELD_DEFAULT, @AUTOVAL
WHILE @@FETCH_STATUS = 0
BEGIN
select @temp = ''
-- now based on the fieldtype we can act
if (@field_type = 'varchar')
begin
--simple string. easy to generate...
select @temp = ''
select @counter = 1
WHILE @counter < @field_size
BEGIN
select @i = dbo.fx_generateRandomNumber(newID(), ascii('1'), ascii('z')) -- from 1 to z
SELECT @temp = @temp + char(@i)
select @counter = @counter + 1
END
end
else if (@field_type = 'int')
begin
--can generate ONLY if is not and ID field...
if (isnull(@autoval,'') = '')
begin
select @temp = cast(dbo.fx_generateRandomNumber(newID(), 1,2^31 - 1) as varchar) -- integer range
end
end
else if (@field_type = 'datetime')
begin
SELECT @temp = cast(
cast (RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) ) * 123456 + @recno
as datetime) as varchar)
end
else if (@field_type = 'bigint')
begin
--can generate ONLY if is not and ID field...
if (isnull(@autoval,'') = '')
begin
select @temp = cast(dbo.fx_generateRandomNumber(newID(), 1,2 ^ 63 - 1) as varchar) -- integer range
end
end
else if (@field_type = 'binary')
begin
select @dummy = 'a'
end
else if (@field_type = 'bit')
begin
select @temp = cast(dbo.fx_generateRandomNumber(newID(), 0, 1 ) as varchar) -- boolean range
end
else if (@field_type = 'char')
begin
select @temp = ''
select @counter = 1
WHILE @counter < @field_size
BEGIN
select @i = dbo.fx_generateRandomNumber(newID(), ascii('1'), ascii('z')) -- from 1 to z
SELECT @temp = @temp + char(@i)
select @counter = @counter + 1
END
end
else if (@field_type = 'decimal')
begin
select @temp = cast(dbo.fx_generateRandomNumber(newID(), 1,10^38-11) as varchar)
end
else if (@field_type = 'float')
begin
select @temp = cast(dbo.fx_generateRandomNumber(newID(), 1,1.79E + 308) as varchar)
end
else if (@field_type = 'image')
begin
select @dummy = 'a'
end
else if (@field_type = 'money')
begin
select @temp = cast(dbo.fx_generateRandomNumber(newID(), 1,2^63-1) as varchar)
end
else if (@field_type = 'nchar')
begin
select @temp = ''
select @counter = 1
WHILE @counter < @field_size
BEGIN
select @i = dbo.fx_generateRandomNumber(newID(), ascii('1'), ascii('z')) -- from 1 to z
SELECT @temp = @temp + char(@i)
select @counter = @counter + 1
END
end
else if (@field_type = 'ntext')
begin
select @temp = ''
select @counter = 1
WHILE @counter < @field_size
BEGIN
select @i = dbo.fx_generateRandomNumber(newID(), ascii('1'), ascii('z')) -- from 1 to z
SELECT @temp = @temp + char(@i)
select @counter = @counter + 1
END
end
else if (@field_type = 'numeric')
begin
select @temp = cast(dbo.fx_generateRandomNumber(newID(), 1,10^38-1) as varchar)
end
else if (@field_type = 'real')
begin
select @temp = cast(dbo.fx_generateRandomNumber(newID(), 1,3.40E+38) as varchar)
end
else if (@field_type = 'smalldatetime')
begin
select @temp = cast(cast(
RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) ) * 123456 + @recno
as smalldatetime) as varchar)
end
else if (@field_type = 'smallmoney')
begin
select @temp = cast(dbo.fx_generateRandomNumber(newID(), 1,+214748.3647) as varchar)
end
else if (@field_type = 'timestamp')
begin
select @temp = cast(getdate() as timestamp)
end
else if (@field_type = 'tinyint')
begin
select @temp = cast(dbo.fx_generateRandomNumber(newID(), 1,255) as varchar)
end
else if (@field_type = 'uniqueidentifier')
begin
select @dummy = 'a'
end
else if (@field_type = 'varbinary')
begin
select @dummy = 'a'
end
update #temp set GeneratedString = @temp
where field_name = @field_name
FETCH NEXT FROM Browser_cursor
INTO @FIELD_NAME, @FIELD_TYPE, @FIELD_SIZE, @FIELD_DEFAULT, @AUTOVAL
End
CLOSE Browser_cursor
DEALLOCATE Browser_cursor
-- now all INSERT inside main table
select @leftsql = 'insert into ' + @tablename + ' ('
select @rightsql = ') values ('
declare makesqlcursor cursor for
select field_name, field_type, generatedstring from #temp
open makesqlcursor
FETCH NEXT FROM makesqlcursor into @FIELD_NAME, @field_type, @value
WHILE @@FETCH_STATUS = 0
Begin
if @field_type <> 'uniqueidentifier'
begin
select @leftsql = @leftsql + @field_name + ','
select @rightsql = @rightsql + 'cast(' +
char(39) + @value + char(39) + ' as ' + @field_type + '), '
-- send the line
end
FETCH NEXT FROM makesqlcursor into @FIELD_NAME, @field_type, @value
End
CLOSE makesqlcursor
DEALLOCATE makesqlcursor
-- trim last comma
select @leftsql = substring(@leftsql,1,len(@leftsql)-1)
select @rightsql = substring(@rightsql,1,len(@rightsql)-1)
select @rightsql = @rightsql + ')'
execute (@leftsql + ' ' + @rightsql)
select @recno = @recno + 1
end -- do it again
END
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 
