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 BY	table_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