CODEWITHSUNDEEP
azurepowershellazure-sql-database
Aaron
Aaron

Reputation: 1455

Write-SqlTableData to import CSV into a Azure SQL Server table

The below command creates a new table, test for me but it doesn't insert any data into it. 

Write-SqlTableData -TableName test -ServerInstance myservername -DatabaseName mydb -SchemaName dbo -Credential $mycreds -InputData $data -Force

This is the error message I get:

Write-SqlTableData : Cannot access destination table '[Mydb].[dbo].
[test]'.
At line:1 char:1
+ Write-SqlTableData -TableName test -ServerInstance myinstance
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : WriteError: ([dbo].[test]:Table) [Write-SqlTableData], InvalidOperationException
    + FullyQualifiedErrorId : WriteToTableFailure,Microsoft.SqlServer.Management.PowerShell.WriteSqlTableData

Any ideas are appreciated.

UPDATE

This is the code to populate data. 

 $data = import-csv 'C:\Users\azure-user\myfile.csv'

This is what the file looks like - 

"State","ProviderNbr","Entity","Address","City","Zip","Phone","Ownership","StarRating"
"AL","017000","ALABAMA DEPARTMENT OF HEALTH HOME CARE","201 MONROE STREET, SUITE 1200", "ALABAMA", "32423", "3233233233", "Alabama", "4"

Upvotes: 1

Views: 4486

Answers (3)

cheezeWhizze
cheezeWhizze

Reputation: 1

This worked well for me for writing to azure sql (with new -AccessToken switch):

$targetDBName="MyDb"
$servername="myazuresqldb.database.windows.net"
$targetTableName="MyNewTable"
$sourceFile = "C:\Temp\MyCSVTable.csv"
#need to install-module sqlserver and install-module Az.Account

Connect-AzAccount
$myToken=Get-AzAccessToken -ResourceUrl https://database.windows.net
# Comma is important!
,(Import-Csv $sourceFile) |Write-SqlTableData -AccessToken $myToken  -ServerInstance $servername -DatabaseName $targetDBName -SchemaName "dbo" -TableName $targetTableName -Force

Upvotes: 0

codeulike
codeulike

Reputation: 23064

This is a weird one - as you say, in Azure Read-SqlTableData works but Write-SqlTableData doesn't. From the discussion on MSDN here I think its something to do with the Azure environment making it hard for the cmdlet to interpret the 'ServerInstance' parameter.

Example 5 in Microsoft's Write-SqlTableData documentation "Write data to an existing table of an Azure SQL Database" shows the way forwards - we need to instantiate an SMO reference to the Table and feed that to the cmdlet instead. Unfortunately the example Microsoft gives contains a small error (you can't do $table = $db.Tables["MyTable1"] to get the table, it doesn't work)

Here's a modified version of that example:

$csvPath = "C:\Temp\mycsv.csv"
$csvDelimiter = ","

# Set your connection string to Azure SQL DB.
$connString = 'Data Source=server;Persist Security Info=True'
$cred = Get-Credential -Message "Enter your SQL Auth credentials"
$cred.Password.MakeReadOnly()
$sqlcred = New-Object -TypeName System.Data.SqlClient.SqlCredential -ArgumentList $cred.UserName,$cred.Password

# Create a SqlConnection and finally get access to the SMO Server object.
$sqlcc = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList  $connString,$sqlcred
$sc = New-Object -TypeName Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList  $sqlcc
$srv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $sc

# Get access to table 'MyTable1' on database 'MyDB'.
# Note: both objects are assumed to exists already.
$db = $srv.Databases["MyDB"]
$tableSmo = $db.Tables | Where-Object {$_.Name -eq "MyTable1"}

# leading comma makes an array with one item
# this makes PowerShell pass the entire contents of the file directly to the Write-SqlTableData cmdlet, which in turn can do a bulk-insert
, (Import-Csv -Path $pcsvPath -Delimiter $csvDelimiter) | Write-SqlTableData  -InputObject $tableSmo 

$sc.Disconnect()

If you're doing integrated security you can miss off all the $cred and $sqlcred stuff and just create the SqlConnection using $sqlcc = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $connString

Note: This worked for me with "A SQL Server running on a VM in Azure", in that I was having the same error as you, 'cannot access destination table' and this approach fixed it. I haven't tested it with an "Azure SQL Database" i.e. SQL Server as a service. But from the microsoft documentation it sounds like this should work.

Upvotes: 1

Jim Xu
Jim Xu

Reputation: 23121

According to my test, we can not use the command Write-SqlTableData to import CSV file to Azure SQL and we just can use it to import CSV file to on-premise SQL enter image description here

So if you want to import CSV file to Azure SQL with powershell, you can use the command Invoke-SQLCmdto insert record on by one. For example:

$Database   = ''

$Server     = '.database.windows.net'

$UserName   = ''

$Password   = ''

$CSVFileName = ''

 $text = "CREATE TABLE [dbo].[Colors2](
    [id] [int] NULL,
    [value] [nvarchar](30) NULL
) "
Invoke-SQLCmd -ServerInstance  $Server  -Database $Database -Username $UserName -Password $Password -Query $text

 $CSVImport = Import-CSV -Path $CSVFileName
 ForEach ($CSVLine in $CSVImport){

 $Id =[int] $CSVLine.Id
 $Vaule=$CSVLine.value
 $SQLInsert = "INSERT INTO [dbo].[Colors2] (id, value)
VALUES('$Id', '$Vaule');"
 Invoke-SQLCmd -ServerInstance  $Server  -Database $Database -Username $UserName -Password $Password -Query $SQLInsert


 }


Invoke-SQLCmd -ServerInstance  $Server  -Database $Database -Username $UserName -Password $Password -Query "select * from [dbo].[Colors2]"

Besides, you also can use other ways ( such as BULK INSERT ) to implement it. For further information, please refer to https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=azuresqldb-current.

Upvotes: 0

Related Questions