Load text into SQL Server via Powershell

Question

I am loading character data read from a file row by row into a SQL table. The table is:

CREATE TABLE [dbo].[PSFileOrder](
    [PSFOrder_Id] [int] IDENTITY(0,1) NOT NULL,
    [PSFile] [varchar](255) NOT NULL,
 CONSTRAINT [PK_PSFileOrder] PRIMARY KEY CLUSTERED 
(
    [PSFOrder_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The powershell code I am using is

#LoadPSFile
$PSFile = "d:\ps\xx.ps1"
cls
$xy = Get-content $PSFile
$xy
foreach($xy in $xy) {invoke-sqlcmd -serverInstance localhost\sqlexpress -query "Insert AA.dbo.PSFileOrder(PSFile) Values ('$xy')"}

If the file I am loading is:

#Filename
#The first line will always be the file name
cls
$filter = "*.*"
$folder = "\\localhost\d$\Master\men and their toys"
$filecount = (Get-ChildItem -literalpath $folder -filter $filter).Countem -literalpath $folder -filter $filter).Countem -literalpath $folder -filter $filter).Countem -literalpath $folder -filter $filter).Count
If ($filecount -lt 1) {$filecount = 0}
"There are {0} files in the {1} directory" -f $filecount, $folder

the file loads without error. If there is a single quote in the text

"There are {0} files in the {1} d'irectory" -f $filecount, $folder

then I will recieve this error

Invoke-Sqlcmd : Incorrect syntax near 'irectory'.
Unclosed quotation mark after the character string ' -f $filecount, $folder')
;'.
At C:\Users\RC\AppData\Local\Temp\2ed13f21-5b46-4df4-a5ee-2488c3dd5ee4.ps1:6 char:35
+ foreach($xy in $xy) {invoke-sqlcmd <<<<  -serverInstance localhost\sqlexpress -query "Insert AA.dbo.PSFileOrder(PSFile) Values ('$xy')"}
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

I do believe the error is caused by the way I have written the "-query" statement using single quotes arount the $xy variable.
My questions are:

Have I crafted the Invoke-sqlcmd correctly?
Is there a substitue for the single quote around $xy?
Is there a better way to load the text?

I want to maintain the one to one relationship of the text file and row in the db.

Thanks RC

Two answers put me on the correct path of doubling the single quote prior to loading to SQL. The revised code is

#LoadPSFile
cls
Get-content "d:\ps\xx.ps1" | 
Foreach-Object {$_ -replace "'", "''"} |
ForEach-Object{invoke-sqlcmd -serverInstance localhost\sqlexpress -query "Insert AA.dbo.PSFileOrder(PSFile) Values ('$_')"}

Roman Kuzmin · Accepted Answer · 2010-12-14 19:42:04Z

5

You get malformed SQL syntax. I believe the quick fix is to replace any single quote with two single quotes in $xy:

$escaped = $xy.Replace("'", "''")
invoke-sqlcmd -serverInstance localhost\sqlexpress -query "Insert AA.dbo.PSFileOrder(PSFile) Values ('$escaped')"

# or (the same but without the intermediate variable):

invoke-sqlcmd -serverInstance localhost\sqlexpress -query @"
Insert AA.dbo.PSFileOrder(PSFile) Values ('$($xy.Replace("'", "''"))')
"@

But this is perhaps not the best way. Unfortunately I am not familiar with invoke-sqlcmd capabilities. If it supports parameterized queries I would go that way and supply $xy value as it is as a parameter value (no preparation of $xy would be needed in such a case).

edited Dec 14, 2010 at 19:42

answered Dec 14, 2010 at 19:35

Roman Kuzmin

42.4k11 gold badges105 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bernd_k Over a year ago

Yes if he has sql-2008, he can pack up to 1000 value rows into one insert statement. Other options are to create ad hoc stored procedures as with code.google.com/p/poshcodegen

RC_Cleland Over a year ago

I am using SQL 2K8R2. I don't need more than this row by row because the data is short.

bernd_k · Accepted Answer · 2010-12-14 19:53:09Z

2

You must double the apostrophes within the string

foreach($xy in $xy) 
{
    $xy = $xy -replace "'", "''"
    invoke-sqlcmd -serverInstance localhost\sqlexpress -query "Insert AA.dbo.PSFileOrder(PSFile) Values ('$xy')"
}

when performance is an issue, you choose some bulkcopy solultion. In the SQLPSX project there is a module adolib.psm1 which contains a function invoke-bulkcopy

link text

edited Dec 14, 2010 at 19:53

answered Dec 14, 2010 at 19:35

bernd_k

12.1k7 gold badges49 silver badges64 bronze badges

Comments

Justin Dearing · Accepted Answer · 2011-09-10 17:56:32Z

2

A better solution would be to use ADO.NET paramaterized queries via the SqlCommand.Parameters property. This will solve your single apostrophe error as well as scrub your input for anything else that could create a sql error.

Unfortunately Invoke-SqlCmd does not allow you to do this. However, I modified Chad Miller's Invoke-SqlCmd2 to allow you to do this. Include Invoke-SqlCmd2.ps1 in your profile and change your script like so:

foreach($xy in $xy) {invoke-sqlcmd -serverInstance localhost\sqlexpress -query "Insert AA.dbo.PSFileOrder(PSFile) Values (@xy)" -SqlParamaters @{ '@xy' = $xy}}

answered Sep 10, 2011 at 17:56

Justin Dearing

15.1k24 gold badges94 silver badges166 bronze badges

Collectives™ on Stack Overflow

Load text into SQL Server via Powershell

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related