Step1: CREATE TABLE dbo.AllTypeData( id INT NOT NULL IDENTITY PRIMARY KEY, IntType int NULL, SmallIntType smallint NULL, BigIntType bigint NULL, TinyIntType tinyint NULL, DecimalType decimal(18,4) NULL, FloatType float NULL, MoneyType money NULL, SmallMoneyType smallmoney NULL, RealType real NULL, NumericType numeric(18,0) NULL, DropdownVal varchar(MAX) NULL, CharType char(10) NULL, NCharType nchar(20) NULL, NVarCharType nvarchar(50) NULL, NVarCharMaxType nvarchar(MAX) NULL, VarCharType varchar(50) NULL, VarCharMaxType varchar(MAX) NULL, UniqueidentifierType uniqueidentifier NULL, BitType bit NULL, DateType date NULL, DateTimeType datetime NULL, SmallDateTimeType smalldatetime NULL, DateTimeOffsetType datetimeoffset(7) NULL, TimeType time(7) NULL ) GO Step2: -- Declare your array table variable DECLARE @MYARRAY table (TEMPCOL nvarchar(50), ARRAYINDEX int identity(1,1) ) -- Add values to your array table, these are the values which you need to look for in your database INSERT INTO @MYARRAY (TEMPCOL) VALUES ('PRD-2222'), ('PRD-3333'), ('PRD-4563'), ('PRD-4569'), ('PRD-6657'), ('PRD-3452'), ('PRD-6578') DECLARE @val AS INT = 1; DECLARE @Upper int =1; DECLARE @Lower int =1; DECLARE @Random int = 1; DECLARE @IntType_start AS INT = -147483648; DECLARE @IntType_end AS INT = 147483647; DECLARE @SmallIntType_start AS smallint = -12768; DECLARE @SmallIntType_end AS smallint = 12767; DECLARE @BigIntType_start AS bigint = -4223372036854775808; DECLARE @BigIntType_end AS bigint = 4223372036854775807; DECLARE @TinyIntType_start AS tinyint = 0; DECLARE @TinyIntType_end AS tinyint = 255; DECLARE @DecimalType_start AS decimal = -10^38 +1; DECLARE @DecimalType_end AS decimal = 10^38 -1; DECLARE @FloatType_start AS float = -1.79E+307; DECLARE @FloatType_end AS float = 1.79E+307; DECLARE @MoneyType_start AS money = -422337203685477.5808; DECLARE @MoneyType_end AS money = 422337203685477.5807; DECLARE @SmallMoneyType_start AS smallmoney = -99748.3648; DECLARE @SmallMoneyType_end AS smallmoney = 99748.3647; DECLARE @RealType_start AS real = -1.40E+38; DECLARE @RealType_end AS real = 1.40E+38; DECLARE @NumericType_start AS numeric = -10^38+1; DECLARE @NumericType_end AS numeric = 10^38-1; --DECLARE @CharType AS char(10) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'; --DECLARE @NCharType AS nchar(20) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~'; DECLARE @NVarCharType AS nvarchar(50) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~'; DECLARE @NVarCharMaxType AS nvarchar(Max) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~'; DECLARE @VarCharType AS varchar(50) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~'; DECLARE @VarCharMaxType AS varchar(Max) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~'; DECLARE @UniqueidentifierType AS uniqueidentifier = NEWID(); DECLARE @DateType AS date; DECLARE @DateTimeType_start datetime = '1753-1-1 00:00:00'; DECLARE @DateTimeType_end datetime = '9000-12-12 23:59:59'; DECLARE @SmallDateTimeType_start smalldatetime = '1900-1-1 00:00:00';; DECLARE @SmallDateTimeType_end smalldatetime = '2079-06-06 00:00:00'; DECLARE @DateTimeOffsetType As datetimeoffset(7); DECLARE @TimeType AS time = '00:00:00.0000000'; SELECT @Upper= COUNT(*) FROM @MYARRAY; WHILE @val <= 10 BEGIN SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) INSERT AllTypeData ( IntType, SmallIntType, BigIntType, TinyIntType, DecimalType, FloatType, MoneyType, SmallMoneyType, RealType, NumericType, DropdownVal, CharType, NCharType, NVarCharType, NVarCharMaxType, VarCharType, VarCharMaxType, UniqueidentifierType, BitType, DateType, DateTimeType, SmallDateTimeType, DateTimeOffsetType, TimeType ) SELECT cast(Rand()*(@IntType_End-@IntType_start)+@IntType_start as int), cast(Rand()*(@SmallIntType_End-@SmallIntType_start)+@SmallIntType_start as smallint), cast(Rand()*(@BigIntType_End-@BigIntType_start)+@BigIntType_start as bigint), cast(Rand()*(@TinyIntType_End-@TinyIntType_start)+@TinyIntType_start as tinyint), Rand()*(@DecimalType_End-@DecimalType_start)+@DecimalType_start as decimal, Rand()*(@FloatType_End-@FloatType_start)+@FloatType_start as float, Rand()*(@MoneyType_End-@MoneyType_start)+@MoneyType_start as money, Rand()*(@SmallMoneyType_end-@SmallMoneyType_start)+@SmallMoneyType_start as SmallMoney, Rand()*(@RealType_end-@RealType_start)+@RealType_start as real, Rand()*(@NumericType_end-@NumericType_start)+@NumericType_start as numeric, (select TEMPCOL from @MYARRAY where ARRAYINDEX = @Random), substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~',(abs(checksum(newid())) % 47)+1, 10), substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~',(abs(checksum(newid())) % 47)+1, 20), substring(@NVarCharType,(abs(checksum(newid()))%49)+1,20), substring(@NVarCharMaxType,(abs(checksum(newid()))%49)+1,20), substring(@VarCharType,(abs(checksum(newid()))%49)+1,20), substring(@VarCharMaxType,(abs(checksum(newid()))%49)+1,20), Newid(), CAST(ROUND(RAND(),0) AS BIT), DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0), DATEADD(MILLISECOND, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800,DATEADD(SECOND, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(day, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*(CAST((@DateTimeType_end - @DateTimeType_start) AS INT )), @DateTimeType_start))), DATEADD(second, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(day, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*(CAST((@SmallDateTimeType_end - @SmallDateTimeType_start) AS INT )), @SmallDateTimeType_start)), DATEADD(MILLISECOND, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(SECOND, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0))), DATEADD(NANOSECOND, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(SECOND, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(MINUTE, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(HH,RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800,cast(@TimeType as time))))); Set @val=@val+1; END select * from AllTypeData