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

Rate

Share

Share

Rate