Write-DataTable

Write-DataTable

Writes data to a SQL Server table.

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

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.)

-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). 

None. You cannot pipe objects to Write-DataTable.

None. Produces no output.

        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

-------------------------- 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.