I'm currently running a loop to dynamicly create an SQL Query. I need to detect which value are empty to put NULL instead of '' in the insert query. I tested my code in Windows Powershell ISE and the result is what I want. But when I test in powershell studio, it doesn't work. What I'm doing is a copy of another SQL row while modifying the first 2 values.
Expected result (and got in powershell ISE):
INSERT INTO dbo.database (Type,ID,C3,...) values ('R','9999',NULL,...)
Result in powershell Studio
INSERT INTO dbo.database (Type,ID,C3,...) values ('R','9999','',...)
The code:
Code: Select all
$ConnectionString = 'Data Source=database.server.fqdn;Initial Catalog=databasetable;Integrated Security=True;User ID=;Password='
$QueryString = "SELECT * from dbo.settings WHERE ID = '1039' and TYPE = 'R'"
$command = New-Object System.Data.SqlClient.SqlCommand ($QueryString, $ConnectionString)
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter ($command)
$dataset = New-Object System.Data.DataSet
[void]$adapter.Fill($dataset)
$sourceSettings = $dataset.Tables[0].Rows[0]
$colCount = $sourceSettings.Table.Columns.Count
$queryInsertTable = ""
$QueryInsertValue = ""
for ($i=0; $i -lt $colCount; $i++){
# Trapping of source Type, overwritting value in insert query
if ($($sourceSettings.Table.Columns.ColumnName[$i]) -eq "Type"){
$queryInsertTable += "'Type'"
$queryInsertValue += "'R'"
# Trapping of source ID, overwritting value in insert query
}elseif ($($sourceSettings.Table.Columns.ColumnName[$i]) -eq "ID"){
$queryInsertTable += "'ID'"
$queryInsertValue += "'9999'"
}else{
# All other value
$queryInsertTable += "'$($sourceSettings.Table.Columns.ColumnName[$i])'"
# If value is null, I want to insert a NULL statement instead of '' (empty value)
if ($($sourceSettings[$i]) -eq ""){
$queryInsertValue += "NULL"
}else{
$queryInsertValue += "'$($sourceSettings[$i])'"
}
}
if ($i+1 -lt $colCount){
$queryInsertTable += ","
$queryInsertValue += ","
}
}
$QueryString = "INSERT INTO dbo.Settings ($queryInsertTable) values ($QueryInsertValue)"
Write-Host $QueryString
Output I get in Powershell studio:INSERT INTO dbo.Settings ('Type','ID','UserID','UserDomain','UserPassword','UDShare','UDDir','UDProfiles','SLShare','SLShareDynamicLogging','EventShare','OSInstall','ComputerName','OSDComputerName','Home_Page','JoinDomain','JoinWorkGroup','DomainAdmin','DomainAdminDomain','DomainAdminPassword','MachineObjectOU','ScanStateArgs','LoadStateArgs','ComputerBackupLocation','BackupShare','BackupDir','UserDataLocation','DoCapture','ProductKey','OverrideProductKey','WDSServer','CaptureGroups','AdminPassword','OrgName','FullName','TimeZone','TimeZoneName','TaskSequenceID','KeyboardLocale','InputLocale','UserLocale','SystemLocale','UILanguage','Xresolution','Yresolution','BitsPerPel','Vrefresh','AreaCode','CountryCode','LongDistanceAccess','Dialing','OSDBitlockerMode','BdeInstallSuppress','BdeInstall','OSDBitLockerStartupKeyDrive','OSDBitLockerCreateRecoveryPassword','OSDBitLockerWaitForEncryption','BDEWaitForEncryption','BdeDriveLetter','BdeDriveSize','BdePin','BdeRecoveryKey','BdeKeyLocation','TpmOwnerPassword','OSDBitLockerRecoveryPassword','OSDBitLockerStartupKey','DriverGroup','ServerA','ServerB','ServerC','ResourceRoot','FinishAction','SMSTSRunCommandLineUserName','SMSTSRunCommandLineUserPassword','USMTConfigFile','WipeDisk','_SMSTSORGNAME','WsusServer','USMT3','DriverSelectionProfile','PackageSelectionProfile','WizardSelectionProfile','AutoMode','AutoUsers','ApplicationSuccessCodes','BackupFile','SkipWizard','SkipCapture','SkipAdminPassword','SkipApplications','SkipComputerBackup','SkipDomainMembership','SkipComputerName','SkipUserData','SkipPackageDisplay','SkipLocaleSelection','SkipProductKey','SkipSummary','SkipFinalSummary','SkipBDDWelcome','SkipTimeZone','SkipTaskSequence','SkipBitLocker','DestinationDisk','DestinationPartition','OSDAdapterCount','OSDAdapter0Name','OSDAdapter0MacAddress','OSDAdapter0EnableDHCP','OSDAdapter0IPAddressList','OSDAdapter0SubnetMask','OSDAdapter0Gateways','OSDAdapter0GatewayCostMetric','OSDAdapter0DNSServerList','OSDAdapter0DNSSuffix','OSDAdapter0EnableDNSRegistration','OSDAdapter0EnableFullDNSRegistration','OSDAdapter0EnableLMHOSTS','OSDAdapter0EnableWINS','OSDAdapter0TcpipNetbiosOptions','OSDAdapter0WINSServerList','OSDAdapter0EnableTCPIPFiltering','OSDAdapter0TCPFilterPortList','OSDAdapter0UDPFilterPortList','OSDAdapter0IPProtocolFilterList','OSDDiskIndex','OSDPartitions','OSDPartitions0TYPE','OSDPartitions0FILESYSTEM','OSDPartitions0BOOTABLE','OSDPartitions0QUICKFORMAT','OSDPartitions0VOLUMENAME','OSDPartitions0SIZE','OSDPartitions0SIZEUNITS','OSDPartitions0VOLUMELETTERVARIABLE','OSDPartitions1TYPE','OSDPartitions1FILESYSTEM','OSDPartitions1BOOTABLE','OSDPartitions1QUICKFORMAT','OSDPartitions1VOLUMENAME','OSDPartitions1SIZE','OSDPartitions1SIZEUNITS','OSDPartitions1VOLUMELETTERVARIABLE','DoNotCreateExtraPartition','OSRoles','OSRoleServices','OSFeatures','ReplicaOrNewDomain','NewDomain','NewDomainDNSName','ParentDomainDNSName','ReplicaDomainDNSName','ChildName','DomainNetBiosName','ForestLevel','DomainLevel','AutoConfigDNS','ConfirmGC','CriticalReplicationOnly','ADDSUserName','ADDSUserDomain','ADDSPassword','ReplicationSourceDC','DatabasePath','ADDSLogPath','SysVolPath','SafeModeAdminPassword','SiteName','DHCPServerOptionRouter','DHCPServerOptionDNSServer','DHCPServerOptionWINSServer','DHCPServerOptionDNSDomain','DHCPServerOptionNBTNodeType','DHCPServerOptionPXEClient','DHCPScopes','DHCPScopes0SubnetMask','DHCPScopes0IP','DHCPScopes0Name','DHCPScopes0Description','DHCPScopes0StartIP','DHCPScopes0EndIP','DHCPScopes0ExcludeStartIP','DHCPScopes0ExcludeEndIP','DHCPScopes0OptionRouter','DHCPScopes0OptionDNSServer','DHCPScopes0OptionWINSServer','DHCPScopes0OptionDNSDomainName','DHCPScopes0OptionNBTNodeType','DHCPScopes0OptionLease','DHCPScopes0OptionPXEClient','DNSZones','DNSZones0Name','DNSZones0Type','DNSZones0MasterIP','DNSZones0DirectoryPartition','DNSZones0FileName','DNSZones0Scavenge','DNSZones0Update','DNSServerOptionDisableRecursion','DNSServerOptionBINDSecondaries','DNSServerOptionFailOnLoad','DNSServerOptionEnableRoundRobin','DNSServerOptionEnableNetmaskOrdering','DNSServerOptionEnableSecureCache','DNSServerOptionNameCheckFlag','SMSTSPreferredAdvertID','SMSTSUDAUsers','SMSTSAssignUsersMode','OSDStateStorePath','A8','A7','A6','A5','A4','A3','A2','A1','C9','C8','C7','C6','C5','C4','C3','C2','C1') values ('R','9999',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'YES',NULL,NULL,NULL,'corpo.stm.info',NULL,'ASDF',NULL,'ASDF','ASDF','ASDFASDF','ASDF',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE','ASDF','N',' ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,' ',NULL,' ','10 ')
INSERT INTO dbo.Settings ('Type','ID','UserID','UserDomain','UserPassword','UDShare','UDDir','UDProfiles','SLShare','SLShareDynamicLogging','EventShare','OSInstall','ComputerName','OSDComputerName','Home_Page','JoinDomain','JoinWorkGroup','DomainAdmin','DomainAdminDomain','DomainAdminPassword','MachineObjectOU','ScanStateArgs','LoadStateArgs','ComputerBackupLocation','BackupShare','BackupDir','UserDataLocation','DoCapture','ProductKey','OverrideProductKey','WDSServer','CaptureGroups','AdminPassword','OrgName','FullName','TimeZone','TimeZoneName','TaskSequenceID','KeyboardLocale','InputLocale','UserLocale','SystemLocale','UILanguage','Xresolution','Yresolution','BitsPerPel','Vrefresh','AreaCode','CountryCode','LongDistanceAccess','Dialing','OSDBitlockerMode','BdeInstallSuppress','BdeInstall','OSDBitLockerStartupKeyDrive','OSDBitLockerCreateRecoveryPassword','OSDBitLockerWaitForEncryption','BDEWaitForEncryption','BdeDriveLetter','BdeDriveSize','BdePin','BdeRecoveryKey','BdeKeyLocation','TpmOwnerPassword','OSDBitLockerRecoveryPassword','OSDBitLockerStartupKey','DriverGroup','ServerA','ServerB','ServerC','ResourceRoot','FinishAction','SMSTSRunCommandLineUserName','SMSTSRunCommandLineUserPassword','USMTConfigFile','WipeDisk','_SMSTSORGNAME','WsusServer','USMT3','DriverSelectionProfile','PackageSelectionProfile','WizardSelectionProfile','AutoMode','AutoUsers','ApplicationSuccessCodes','BackupFile','SkipWizard','SkipCapture','SkipAdminPassword','SkipApplications','SkipComputerBackup','SkipDomainMembership','SkipComputerName','SkipUserData','SkipPackageDisplay','SkipLocaleSelection','SkipProductKey','SkipSummary','SkipFinalSummary','SkipBDDWelcome','SkipTimeZone','SkipTaskSequence','SkipBitLocker','DestinationDisk','DestinationPartition','OSDAdapterCount','OSDAdapter0Name','OSDAdapter0MacAddress','OSDAdapter0EnableDHCP','OSDAdapter0IPAddressList','OSDAdapter0SubnetMask','OSDAdapter0Gateways','OSDAdapter0GatewayCostMetric','OSDAdapter0DNSServerList','OSDAdapter0DNSSuffix','OSDAdapter0EnableDNSRegistration','OSDAdapter0EnableFullDNSRegistration','OSDAdapter0EnableLMHOSTS','OSDAdapter0EnableWINS','OSDAdapter0TcpipNetbiosOptions','OSDAdapter0WINSServerList','OSDAdapter0EnableTCPIPFiltering','OSDAdapter0TCPFilterPortList','OSDAdapter0UDPFilterPortList','OSDAdapter0IPProtocolFilterList','OSDDiskIndex','OSDPartitions','OSDPartitions0TYPE','OSDPartitions0FILESYSTEM','OSDPartitions0BOOTABLE','OSDPartitions0QUICKFORMAT','OSDPartitions0VOLUMENAME','OSDPartitions0SIZE','OSDPartitions0SIZEUNITS','OSDPartitions0VOLUMELETTERVARIABLE','OSDPartitions1TYPE','OSDPartitions1FILESYSTEM','OSDPartitions1BOOTABLE','OSDPartitions1QUICKFORMAT','OSDPartitions1VOLUMENAME','OSDPartitions1SIZE','OSDPartitions1SIZEUNITS','OSDPartitions1VOLUMELETTERVARIABLE','DoNotCreateExtraPartition','OSRoles','OSRoleServices','OSFeatures','ReplicaOrNewDomain','NewDomain','NewDomainDNSName','ParentDomainDNSName','ReplicaDomainDNSName','ChildName','DomainNetBiosName','ForestLevel','DomainLevel','AutoConfigDNS','ConfirmGC','CriticalReplicationOnly','ADDSUserName','ADDSUserDomain','ADDSPassword','ReplicationSourceDC','DatabasePath','ADDSLogPath','SysVolPath','SafeModeAdminPassword','SiteName','DHCPServerOptionRouter','DHCPServerOptionDNSServer','DHCPServerOptionWINSServer','DHCPServerOptionDNSDomain','DHCPServerOptionNBTNodeType','DHCPServerOptionPXEClient','DHCPScopes','DHCPScopes0SubnetMask','DHCPScopes0IP','DHCPScopes0Name','DHCPScopes0Description','DHCPScopes0StartIP','DHCPScopes0EndIP','DHCPScopes0ExcludeStartIP','DHCPScopes0ExcludeEndIP','DHCPScopes0OptionRouter','DHCPScopes0OptionDNSServer','DHCPScopes0OptionWINSServer','DHCPScopes0OptionDNSDomainName','DHCPScopes0OptionNBTNodeType','DHCPScopes0OptionLease','DHCPScopes0OptionPXEClient','DNSZones','DNSZones0Name','DNSZones0Type','DNSZones0MasterIP','DNSZones0DirectoryPartition','DNSZones0FileName','DNSZones0Scavenge','DNSZones0Update','DNSServerOptionDisableRecursion','DNSServerOptionBINDSecondaries','DNSServerOptionFailOnLoad','DNSServerOptionEnableRoundRobin','DNSServerOptionEnableNetmaskOrdering','DNSServerOptionEnableSecureCache','DNSServerOptionNameCheckFlag','SMSTSPreferredAdvertID','SMSTSUDAUsers','SMSTSAssignUsersMode','OSDStateStorePath','A8','A7','A6','A5','A4','A3','A2','A1','C9','C8','C7','C6','C5','C4','C3','C2','C1') values ('R','9999','','','','','','','','','','YES','','','','ASDF','','ASDF','ASDF','ASDFASDF','ASDF','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','TRUE','ASDF','N','','','','','','','','','','','','','','10 ')