CODEWITHSUNDEEP
sql-serverpowershell
chris1982
chris1982

Reputation: 275

Calling a TRUNCATE and BULK INSERT INTO SQL command from Powershell

I am trying to run a simple TRUNCATE and BULK INSERT into SQL from Powershell. Are these commands possible?

$server = "TESTING"
$database = "TESTAREA"
$connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database;")

$query = "TRUNCATE TABLE [TESTAREA].dbo.[client_lookup]"

$query = "BULK INSERT [TESTAREA].dbo.[client_lookup]
FROM '\\fss\share\Accounts\Lookup_Tables\Client_Lookup.csv'
WITH
(
KEEPIDENTITY,
FIRSTROW = 2,
FIELDTERMINATOR= ',',
ROWTERMINATOR = '0x0a'
)"

$connection.Close()

Both of these queries execute in SQL and I have used a similar Powershell script to run SQL queries in the past (not BULK INSERT or TRUNCATE). I am fairly new to Powershell, am I missing something?

Upvotes: 2

Views: 11823

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46203

You need to create a SqlCommand object and execute the script on an open connection. Refactored example:

$server = "TESTING"
$database = "TESTAREA"
$connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database;")

$query = "TRUNCATE TABLE dbo.client_lookup;
BULK INSERT dbo.client_lookup
FROM '\\fss\share\Accounts\Lookup_Tables\Client_Lookup.csv'
WITH
(
KEEPIDENTITY,
FIRSTROW = 2,
FIELDTERMINATOR= ',',
ROWTERMINATOR = '0x0a'
)";

$connection.Open();
$command = new-object System.Data.SqlClient.SqlCommand($query, $connection);
$command.ExecuteNonQuery();
$connection.Close();

Upvotes: 6

Dan Field
Dan Field

Reputation: 21641

You're missing code that would actually run the query!

Try the Invoke-Sqlcmd cmdlet:

$query = "TRUNCATE TABLE [TESTAREA].dbo.[client_lookup];

BULK INSERT [TESTAREA].dbo.[client_lookup]
FROM '\\fss\share\Accounts\Lookup_Tables\Client_Lookup.csv'
WITH
(
KEEPIDENTITY,
FIRSTROW = 2,
FIELDTERMINATOR= ',',
ROWTERMINATOR = '0x0a'
)"

Set-Location SQLSERVER:\SQL\TESTING\\databases\TESTAREA
Invoke-Sqlcmd -Query $query

Upvotes: 0

Related Questions