CleanCode logo
sitemap
SEARCH:

CleanCode PowerShell Libraries v1.2.05 API: CleanCode » SqlTools » Write-DataTable

Write-DataTable

NAME

Write-DataTable

SYNOPSIS

Writes data to a SQL Server table.

SYNTAX

Write-DataTable [[-ServerInstance] <String>] [[-Database] <String>] [-TableName] <String> [-Data] <Object> [[-Username] <String>] [[-Password] <String>] [[-BatchSize] <Int32>] [[-QueryTimeout] <Int32>] [[-ConnectionTimeout] <Int32>] [<CommonParameters>]

DESCRIPTION

Writes data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with an IDataReader instance. (Based on Chad Miller's original work--see Links section.)

PARAMETERS

-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 Write-DataTable attempts to infer it:
        * If on a SQLSERVER: drive (or alias) and the path specifies the SQL folder,
        Write-DataTable 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, Write-DataTable uses the server and instance
        specified in that path.

        Required?                    false
        Position?                    1
        Default value                
        Accept pipeline input?       false
        Accept wildcard characters?  false

-Database <String>
        A character string specifying the name of a database. Write-DataTable connects to
        this database in the instance specified by -ServerInstance.
        If -Database is not specified Write-DataTable attempts to infer it:
        * If on a SQLSERVER: drive (or alias) and the path specifies both the SQL folder
        and a database name, Write-DataTable 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, Write-DataTable uses
        the database specified in that path.

        Required?                    false
        Position?                    2
        Default value                
        Accept pipeline input?       false
        Accept wildcard characters?  false

-TableName <String>
        Name of the table to create on the SQL Server instance.

        Required?                    true
        Position?                    3
        Default value                
        Accept pipeline input?       false
        Accept wildcard characters?  false

-Data <Object>
        A System.Data.DataTable containing the data to write to the SQL table.

        Required?                    true
        Position?                    4
        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, Write-DataTable
        attempts a Windows Authentication connection using the Windows account running
        the PowerShell session.  When possible, use Windows Authentication.

        Required?                    false
        Position?                    5
        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?                    6
        Default value                
        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?                    7
        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?                    8
        Default value                30
        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?                    9
        Default value                15
        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). 

INPUTS

None. You cannot pipe objects to Write-DataTable.

OUTPUTS

None. Produces no output.

NOTES


        Write-DataTable uses the SqlBulkCopy class see links for additional information on this class. 
        Version History 
        v1.0   - Chad Miller - Initial release 
        v1.1   - Chad Miller - Fixed error message

EXAMPLES

-------------------------- EXAMPLE 1 --------------------------
C:\PS>$dt = Invoke-Sqlcmd -ServerInstance "Z003\R2" -Database pubs "select * from authors"; Write-DataTable -ServerInstance "Z003\R2" -Database pubscopy -TableName authors -Data $dt

This example loads a variable dt of type DataTable from query and write the datatable to another database. This requires the target table to already exist. Use Add-SqlTable to create the table if necessary.

RELATED LINKS

This documentation set was created with CleanCode's DocTreeGenerator.

Valid XHTML 1.0!Valid CSS!Get CleanCode at SourceForge.net. Fast, secure and Free Open Source software downloads
Copyright © 2001-2014 Michael Sorens • Contact usPrivacy Policy
Usage governed by Mozilla Public License 1.1 and CleanCode Courtesy License
CleanCode -- The Website for Clean DesignRevised 2014.07.15