Out-SqlTable

Out-SqlTable

Creates a SQL Server table from Powershell objects.

Out-SqlTable [-InputObject] <PSObject[]> [-TableName] <String> [-ServerInstance <String>] [-Database <String>] [-Username <String>] [-Password <String>] [-MaxLength <Int32>] [-RowId <String>] [-DropExisting] [-BatchSize <Int32>] [-QueryTimeout <Int32>] [-ConnectionTimeout <Int32>] [<CommonParameters>]

Out-SqlTable is simply a composition of Out-DataTable, Add-SqlTable, and Write-DataTable.

-InputObject <PSObject[]>

        Required?                    true

        Position?                    1

        Default value                

        Accept pipeline input?       true (ByValue)

        Accept wildcard characters?  false

-TableName <String>

        Name of the table to create on the SQL Server instance.

        Required?                    true

        Position?                    2

        Default value                

        Accept pipeline input?       false

        Accept wildcard characters?  false

-ServerInstance <String>

        Character string or SMO server object specifying the name of an instance of

        the Database Engine. For default instances, only specify the computer name:

        "MyComputer". For named instances, use the format "ComputerName\InstanceName".

        If -ServerInstance is not specified Out-SqlTable attempts to infer it:

        * If on a SQLSERVER: drive (or alias) and the path specifies the SQL folder,

        Out-SqlTable uses the server and instance specified in the path.

        * If not on a SQLSERVER: drive but the current location on your SQLSERVER:

        drive specifies the SQL folder, Out-SqlTable uses the server and instance

        specified in that path.

        Required?                    false

        Position?                    named

        Default value                

        Accept pipeline input?       false

        Accept wildcard characters?  false

-Database <String>

        A character string specifying the name of a database. Out-SqlTable connects to

        this database in the instance specified by -ServerInstance.

        If -Database is not specified Out-SqlTable attempts to infer it:

        * If on a SQLSERVER: drive (or alias) and the path specifies both the SQL folder

        and a database name, Out-SqlTable uses the database specified in the path.

        * If not on a SQLSERVER: drive but the current location on your SQLSERVER:

        drive specifies both the SQL folder and a database name, Out-SqlTable uses

        the database specified in that path.

        Required?                    false

        Position?                    named

        Default value                

        Accept pipeline input?       false

        Accept wildcard characters?  false

-Username <String>

        Specifies the login ID for making a SQL Server Authentication connection

        to an instance of the Database Engine. The password must be specified

        using -Password. If -Username and -Password are not specified, Out-SqlTable

        attempts a Windows Authentication connection using the Windows account running

        the PowerShell session.  When possible, use Windows Authentication.

        Required?                    false

        Position?                    named

        Default value                

        Accept pipeline input?       false

        Accept wildcard characters?  false

-Password <String>

        Specifies the password for the SQL Server Authentication login ID specified

        in -Username. Passwords are case-sensitive.

        When possible, use Windows Authentication.

        SECURITY NOTE: If you type -Password followed by your password, the password

        is visible to anyone who can see your monitor. If you use -Password in

        a .ps1 script, anyone reading the script file will see your password.

        Assign appropriate permissions to the file to allow only authorized users

        to read the file.

        Required?                    false

        Position?                    named

        Default value                

        Accept pipeline input?       false

        Accept wildcard characters?  false

-MaxLength <Int32>

        Capacity for VarChar and VarBinary columns (limited to 8000 maximum).

        Any data longer than this defined value will be truncated when inserted.

        Required?                    false

        Position?                    named

        Default value                0

        Accept pipeline input?       false

        Accept wildcard characters?  false

-RowId <String>

        Serving as both a switch and a column name, specifying -RowId adds

        an identity column to the table with the value supplied as the column name.

        Required?                    false

        Position?                    named

        Default value                

        Accept pipeline input?       false

        Accept wildcard characters?  false

-DropExisting [<SwitchParameter>]

        If enabled, Out-SqlTable will check whether the table exists before

        attempting to create it. If it does exist, it is first dropped.

        Attempting to use Out-SqlTable without this switch when the table already

        exists generates an error.

        Required?                    false

        Position?                    named

        Default value                False

        Accept pipeline input?       false

        Accept wildcard characters?  false

-BatchSize <Int32>

        Number of rows to send to server at one time (set to 0 to send all rows).

        This parameter maps to the same named parameter in the .NET class SqlBulkCopy.

        Required?                    false

        Position?                    named

        Default value                0

        Accept pipeline input?       false

        Accept wildcard characters?  false

-QueryTimeout <Int32>

        Number of seconds for batch to complete before failing. Though not advised,

        you can use 0 to indicate no limit.

        This parameter maps to the BulkCopyTimeout parameter in the .NET class SqlBulkCopy.

        Required?                    false

        Position?                    named

        Default value                0

        Accept pipeline input?       false

        Accept wildcard characters?  false

-ConnectionTimeout <Int32>

        Number of seconds for connection to complete before failing. Though not advised,

        you can use 0 to indicate no limit.

        Required?                    false

        Position?                    named

        Default value                0

        Accept pipeline input?       false

        Accept wildcard characters?  false

<CommonParameters>

        This cmdlet supports the common parameters: Verbose, Debug,

        ErrorAction, ErrorVariable, WarningAction, WarningVariable,

        OutBuffer and OutVariable. For more information, see 

        about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216). 

Object. Any object can be piped to Out-SqlTable.

None. Produces no output.

        This function is part of the CleanCode toolbox

        from http://cleancode.sourceforge.net/.

        Since CleanCode 1.1.05.

-------------------------- EXAMPLE 1 --------------------------

C:\PS>Get-Process | select ProcessName, Handle | Out-SqlTable -TableName "processes"

Puts selected columnar output from Get-Process into a table in the current database, where the "current database" is specifed by the current location on a SQLSERVER: drive. This is equivalent to these three consecutive commands:

        $dt = Get-Process | select ProcessName, Handle | Out-DataTable

        Add-SqlTable -TableName "processes" -DataTable $dt

        Write-DataTable -TableName "processes" -Data $dt

-------------------------- EXAMPLE 2 --------------------------

C:\PS>ps | select ProcessName, Handle | Out-SqlTable -TableName "processes" -DropExisting -RowId "MyId"

Puts selected columnar output from Get-Process into a table in the current database, dropping the table first if it exists, and adding an identity column "MyId".

-------------------------- EXAMPLE 3 --------------------------

C:\PS>Get-SvnLog . | Out-SqlTable -TableName "svndata" -MaxLength 3000

Puts the output of the CleanCode Get-SvnLog cmdlet into a database table, allowing string fields to be up to 3000 characters rather than the default 1000. This is useful primarily for the msg property of Subversion data.