I have several tables that need to be periodically cleaned (de-duplicated). I want to create a function I can call and pass in a table name as a parameter. I'm stuck on the appropriate use of back tics (`), quotes (") and parentheses to reference the inline scripting variable ($) I'm trying to pass in as a function parameter.
I have poured over "get-help invoke-sqlcmd -detailed" and tried various combinations with the -Variable options, but with no success.
Can someone help me with the correct syntax?
My stripped down script looks like this -
# Define Server
$server = "AHDC389"
# Define Database
$dbname = "VBP"
# Define Table
$dbtab = "[FacilityBaseline]"
# Import SQL svr handler
Import-Module “sqlps” -DisableNameChecking
# Set env for SQL svr handler
Set-Location SQLSERVER:\SQL\$server\DEFAULT\Databases\$dbname
# Define my SQL query
$query = "SELECT DISTINCT * INTO #Temp FROM `$dbtab; TRUNCATE TABLE `$dbtab;
INSERT INTO `$dbtab SELECT DISTINCT * FROM #Temp;"
# Put Query in a Function for reuse
Function DeDup ([string] $dbtab) {
Invoke-Sqlcmd -Query $query
}
# Call my Function and pass the Table name I want to dedup.
DeDup $dbtab
The errors I'm getting are " Incorrect syntax near '$dbtab' " or " 'dbtab' scripting variable not defined '