I am attempting to use dbatools to allow me to import a CSV file into a global temp table under tempdb on SQL Server.
Below is the powershell script I am using. When I run the script it prompts for the path for the CSV file, file delimiter, DB Instance. After entering those items the CSV data is imported to $data, and the Write-DbaDataTable executes and I am prompted for the Table name.
After entering the global temp table name I see the global temp table in SQL under tempdb, and then the insert process occurs. Once the insert process is complete the table disappears from SQL.
I am not sure what I need to change in the script to get the global temp table to stay in SQL.
$File = Read-Host -Prompt "Enter Path to File"
if ($File )
{
Write-Host "File: [$File]"
}
else {
Read-Host -Prompt "Enter Path to File"
}
$Delimiter = Read-Host -Prompt "Enter Delimiter"
if ($File )
{
Write-Host "Delimiter: [$Delimiter]"
}
else {
Read-Host -Prompt "Enter Path to File"
}
$Instance = Read-Host -Prompt "Enter Db instance"
if ($Instance )
{
Write-Host "Instance: [$Instance]"
}
else {
Read-Host -Prompt "Enter Db instance"
}
$Database = "tempdb"
$data = Import-Csv -Path $File -Delimiter $Delimiter | ConvertTo-DbaDataTable
for ($i = 1; $i -le 100; $i++ )
{
Write-Progress -Activity "Search in Progress" -Status "$i% Complete:" -PercentComplete $i
Start-Sleep -milliseconds 250
}
Write-DbaDataTable -SqlInstance $Instance -Database $Database -InputObject $data -AutoCreateTable
for ($i = 1; $i -le 100; $i++ )
{
Write-Progress -Activity "Search in Progress" -Status "$i% Complete:" -PercentComplete $i
Start-Sleep -milliseconds 250
}
It is almost like SQL isn’t keeping the instance of the global temp table. Can an imports be done to a global temporary table in a SQL database where you can then access the global temp table in SQL after the Powershell script is done? What am I missing?