Add-SqlTable

Add-SqlTable

Creates a SQL Server table from a DataTable.

Add-SqlTable [[-ServerInstance] <String>] [[-Database] <String>] [-TableName] <String> [-DataTable] <DataTable> [[-Username] <String>] [[-Password] <String>] [[-MaxLength] <Int32>] [[-RowId] <String>] [[-AsScript]] [[-DropExisting]] [<CommonParameters>]

Creates a SQL Server table from a DataTable using SMO. (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 Add-SqlTable attempts to infer it:

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

        Add-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, Add-SqlTable 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. Add-SqlTable connects to

        this database in the instance specified by -ServerInstance.

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

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

        and a database name, Add-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, Add-SqlTable 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

-DataTable <DataTable>

        A System.Data.DataTable from which the SQL table's schema is derived:

        the names and data types of the columns in the DataTable are used

        to define 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, Add-SqlTable

        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

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

        Default value                1000

        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?                    8

        Default value                

        Accept pipeline input?       false

        Accept wildcard characters?  false

-AsScript [<SwitchParameter>]

        If enabled, Add-SqlTable generates a script to create a table

        without actually creating it.

        Required?                    false

        Position?                    9

        Default value                False

        Accept pipeline input?       false

        Accept wildcard characters?  false

-DropExisting [<SwitchParameter>]

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

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

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

        exists generates an error.

        Required?                    false

        Position?                    10

        Default value                False

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

None.

        Add-SqlTable uses SQL Server Management Objects (SMO).

        SMO is installed with SQL Server Management Studio and is available  

        as a separate download:

        http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52  

        Version History  

        v1.0   - Chad Miller - Initial Release  

        v1.1   - Chad Miller - Updated documentation 

        v1.2   - Chad Miller - Add loading Microsoft.SqlServer.ConnectionInfo 

        v1.3   - Chad Miller - Added error handling 

        v1.4   - Chad Miller - Add VarCharMax and VarBinaryMax handling 

        v1.5   - Chad Miller - Added AsScript switch to output script instead of creating table 

        v1.6   - Chad Miller - Updated Get-SqlType types

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

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

This example loads a variable dt of type DataTable from a query and creates an empty SQL Server table.

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

C:\PS>$dt = Get-Alias | Out-DataTable; Add-SqlTable -ServerInstance "Z003\R2" -Database pubscopy -TableName alias -DataTable $dt

This example creates a DataTable from the properties of Get-Alias and creates an empty SQL Server table.