Import CSV file to global temp table in SQL

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?

You will need to find a way to persist the connection until you’re done doing what you want to do. If no one else uses the table, and you disconnect, the table will be deleted.:

If you create the global temporary table ##employees , any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it after you disconnect and after all other sessions are no longer actively using it.

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms177399(v=sql.105)#temporary-tables

Thanks Matt! I was afraid of that. Is it possible to make the connection persist from within powershell?

I think, from the dbatools examples, you need to use Connect-DbaInstance. You assign the connection to a variable, then pass that variable to the -SqlInstance parameter of Write-DbaDataTable. The connection should persist until you call Disconnect-DbaInstance.

That worked perfectly! Appreciate the insight.

1 Like