This repository has been archived by the owner on Oct 14, 2020. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Gen-PopulatedTbl-LG1.ps1.minified.ps1
10 lines (10 loc) · 2.92 KB
/
Gen-PopulatedTbl-LG1.ps1.minified.ps1
1
2
3
4
5
6
7
8
9
10
${Gen-PopulatedTbl-LG1.ps1} = {function Sql-DataGenerator{param([Parameter(Mandatory=$true)]$limitation)[string[]]$namearr1=@("Jon","Jan","Yen","Yan","Ian","Max","Mac","Mile","Mill","Bo","Bob","Bra","Nic","Nis","Tas","Tess","Kla","Yos","Yosh","Jos","Josh","Geo","Je","Jen","Joe");[string[]]$namearr2=@("Hon","Nel","Us","Is","Iz","Cal","El","Ys","Os","Oss","Ds","Son","Ols","Iles","Orge","Ogre","Hau","Heu","An","Ann","Mel","Lie","Li","Azz","Sho","Sha","Kel");[PSObject[]]$records=@();$rndtok=[System.Random]::new();$location='';[int[]] $statusNum=@(25,23,26,1,45,3,54,543,654,11,555);$Male=0;for($i=0;$i -ne $limitation;$i++){$initCode=$rndtok.Next(0,$namearr1.Count -1)+($i*30);$fname='{0}{1}'-f$namearr1[$rndtok.Next(0,$namearr1.Count -1)],$namearr2[$rndtok.Next(0,$namearr2.Count -1)];$lname='{0}{1}'-f$namearr2[$rndtok.Next(0,$namearr2.Count -1)],$namearr1[$rndtok.Next(0,$namearr1.Count -1)];$addr="Hd"+$namearr1[$rndtok.Next(0,$namearr1.Count -1)]+"ve"+$namearr2[$rndtok.Next(0,$namearr1.Count -1)]+"at";$addrnum=$rndtok.Next(0,10)+2;if($addr-match'i'){$location='Street.'}else{$location='Road'};$address='{0} {1} {2}'-f$addrnum,$addr,$location;$stat=$statusNum[$rndtok.Next(0,$statusNum.Count - 1)]+($rndtok.Next(0,300));if(($rndtok.Next(0,$namearr1.Count -1))%2-eq0){$Male=0}else{$Male=1};$records+=New-Object PSObject -Property @{code=$initCode;firstname=$fname;lastname=$lname;address=$address;status=$stat;male=$Male;date=(Get-Date)}};return $records};function Sql-TblCreator{[CmdLetBinding()]param([Parameter(Mandatory=$false)][string]$SrvName,[Parameter(Mandatory=$true)][string]$DBName,[Parameter(Mandatory=$true)][string]$TableName,[Parameter(Mandatory=$false)][bigint]$limit)if((($SrvName-eq$null)-or($Args.Count-lt4))-or(($Args[0]-eq$null)-or($Args[0]-eq''))){$SrvName=$env:COMPUTERNAME+'\SQLEXPRESS'};if(($limit-eq$null)-or($limit-eq0)){$limit=10};$DMLStatement='';$DDLStatement=@'
CREATE TABLE [dbo].[{0}](
Code BIGINT PRIMARY KEY NOT NULL,
FirstName NVARCHAR(40) NOT NULL,
LastName NVARCHAR(40) NOT NULL,
Address NVARCHAR(100) NOT NULL,
Status INT NOT NULL,
Male Bit,
TimeOfCreation NVARCHAR(200) NOT NULL)
'@-f$TableName;$Srvpath='SQLSERVER:\SQL';Set-Location -Path $Srvpath;Invoke-Sqlcmd -Query $DDLStatement -Database $DBName -ServerInstance $SrvName -ConnectionTimeout 10;$part1="INSERT INTO {0} (Code,FirstName,LastName,Address,Status,Male,TimeOfCreation)`r`n"-f$TableName;$DMLStatement=$DMLStatement+($part1);$outobj=(Sql-DataGenerator -limitation $limit);$i=0;$outobj|% {$part2="SELECT {0},'{1}','{2}','{3}',{4},{5},'{6}'`r`n"-f$_.code,$_.firstname,$_.lastname,$_.address,$_.status,$_.male,$_.date;$part3="UNION ALL`r`n";$i=($outobj.IndexOf($_));if($i-eq$limit-1){$part3="UNION ALL"-replace"UNION ALL",""};$DMLStatement=$DMLStatement+($part2+$part3);$i++};Invoke-Sqlcmd -Query $DMLStatement -Database $DBName -DisableVariables -ServerInstance $SrvName -QueryTimeout ([Int]::MaxValue)};Sql-TblCreator -SrvName '' -DBName 'YourDB' -TableName 'RandomTbl' -limit 1000}