Search code examples
azurepowershellazure-sql-database

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"
Solution

  • 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.