Blog

Some of the output from PowerShell cmdlets return results that can be represented as tables. So how can these results be stored in SQL Server tables?

There are some alternative ways to achieve this, but in this post I’ll use Table-Valued Parameters (introduced in SQL Server 2008). The reason for using TVP’s is that I want to send the results to the database in one single call to the database instead of iterating through the rows and repeating an insert statement.

Let’s use the contents of a file folder as the table:

Get-ChildItem C:\Temp\Demo

But before we start with the PowerShell script we need to create some database objects that can be used to store the information about files and folders. Run the following T-SQL to create the PSToSQLDemo database:

CREATE DATABASE PSToSQLDemo
GO

USE PSToSQLDemo
GO

/* The table type to be used as inparameter for the stored procedure */
CREATE TYPE FolderContentsType AS TABLE
	(
		Mode VARCHAR(10) NOT NULL
		,LastWriteTime DATETIME NOT NULL
		,Length INT NULL
		,Name NVARCHAR(128) NOT NULL
	)
GO

/* The table to store the results */
CREATE TABLE FolderContents
	(
		FolderContentId INT IDENTITY(1,1) NOT NULL CONSTRAINT FolderContentId_PK PRIMARY KEY CLUSTERED
		,Mode VARCHAR(10) NOT NULL
		,LastWriteTime DATETIME NOT NULL
		,Length INT NULL
		,Name NVARCHAR(128) NOT NULL
	)
GO

/* The stored procedure with a TVP inparameter that will be called from the PowerShell script */
CREATE PROCEDURE dbo.InsertFolderContents (@FolderContents FolderContentsType READONLY)

AS

SET NOCOUNT ON

INSERT FolderContents (Mode, LastWriteTime, Length, Name)
SELECT Mode, LastWriteTime, Length, Name
FROM @FolderContents
GO

If you want to test the database objects, run the following T-SQL:

DECLARE @FolderContents FolderContentsType
INSERT @FolderContents VALUES
	('d----', GETDATE(), 12345, 'ABC'),
	('-a--- ', GETDATE(), 789, 'DEF')

EXEC dbo.InsertFolderContents @FolderContents

SELECT * FROM FolderContents

That’s all we need to prepare SQL Server so let’s continue with the PowerShell script. First we need is a DataTable that has the same columns as in the database table above (except for the primary key):

$FolderTable = New-Object System.Data.DataTable
$FolderTable.Columns.Add("Mode", "System.String") | Out-Null
$FolderTable.Columns.Add("LastWriteTime", "System.DateTime") | Out-Null
$FolderTable.Columns.Add("Length", "System.Int32") | Out-Null
$FolderTable.Columns.Add("Name", "System.String") | Out-Null

Next, we load all the folder contents to a variable. Then we iterate through the files and folders in the variable and add them to the datatable:

$files = Get-ChildItem C:\Temp\Demo

foreach ($file in $files)
{
    $FolderTable.Rows.Add($file.Mode, $file.LastWriteTime, $file.Length, $file.Name) | Out-Null
}

Now we have a populated datatable that can be used as input to the stored procedure:

Import-Module SQLPS

$ServerInstance = "MSI\SQL2016"
$DatabaseName = "PSToSQLDemo"

Set-Location SQLSERVER:\SQL\$ServerInstance

$ConnString = "Server=$ServerInstance;Database=$DatabaseName;Integrated Security=SSPI;"

$conn = New-Object System.Data.SqlClient.SqlConnection $ConnString
$conn.Open()

$query = "dbo.InsertFolderContents"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmd.CommandText = $Query
$cmd.Parameters.Add("@FolderContents", [System.Data.SqlDbType]::Structured) | Out-Null
$cmd.Parameters["@FolderContents"].Value = $FolderTable

$cmd.ExecuteNonQuery() | Out-Null
$conn.Close()

The complete script:

cls
Import-Module SQLPS

$ServerInstance = "MSI\SQL2016"
$DatabaseName = "PSToSQLDemo"

Set-Location SQLSERVER:\SQL\$ServerInstance

$FolderTable = New-Object System.Data.DataTable
$FolderTable.Columns.Add("Mode", "System.String") | Out-Null
$FolderTable.Columns.Add("LastWriteTime", "System.DateTime") | Out-Null
$FolderTable.Columns.Add("Length", "System.Int32") | Out-Null
$FolderTable.Columns.Add("Name", "System.String") | Out-Null

$files = Get-ChildItem C:\Temp

foreach ($file in $files)
{
    $FolderTable.Rows.Add($file.Mode, $file.LastWriteTime, $file.Length, $file.Name) | Out-Null
}

$ConnString = "Server=$ServerInstance;Database=$DatabaseName;Integrated Security=SSPI;"

$conn = New-Object System.Data.SqlClient.SqlConnection $ConnString
$conn.Open()

$query = "dbo.InsertFolderContents"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmd.CommandText = $Query
$cmd.Parameters.Add("@FolderContents", [System.Data.SqlDbType]::Structured) | Out-Null
$cmd.Parameters["@FolderContents"].Value = $FolderTable

$cmd.ExecuteNonQuery() | Out-Null
$conn.Close()

Remember to change the folder path to something that exists on your computer. Also change the instance name from MSI\SQL2016 to your own instance name.

The cmdlet Get-ChildItem can do a lot more than in the demostration above. Check out TechNet to see its full capabilities.