How can I create a database table from a system.Data.datatable object?

Let us assume, that I have the following PowerShell code:

$connectionstring = "... some connection ..."
$sql = "Select * from Sometable"
$tablename= 'Copy_of_Sometable' 

$cmd = New-Object system.Data.OleDb.OleDbCommand($sql,$connectionstring)
$da = New-Object system.Data.OleDb.OleDbDataAdapter($cmd)
$dt = New-Object system.Data.datatable
[void]$da.fill($dt)

I know, that I can derive a create table script from $dt and use it to create a new table with a given name cf. for example.

But is there a more direct way to create a new table with a supplied name (empty or already filled) from开发者_运维知识库 the datatable object?

I created a function called add-sqltable which will take a datatable as input and create a SQL Server table using SMO.

The function is available on PoshCode and Scripting Guy Script Repository. I blogged about it for a Scripting Guy guest post.

Using a System.Data.Datatable you pretty much have to use the method described.

However a more direct method is available if you're using SQL 2008. The Invoke-SqlCmd cmdlet will let you do as Ekkehard.Horner suggested SELECT * INTO newtable FROM oldtable. (sadly copy-item isn't supported)

Of course the system.Data.OleDb.OleDbCommand works to do the same thing.

Depending on the driver's capabilities, you may succeed with a single SQL statement like:

"SELECT * INTO newtable FROM oldtable"

With ODBC and Jet the tables may even be IN (keyword) different databases.

Because I really like the "INSERT/SELECT INTO IN" command(s) and never before used Powershell for database 'work':

(Edited) Powershell session:

  PS C:\Documents and Settings\eh\My Documents\WindowsPowerShell>
    $CS="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<SOMEWHERE>\Nwind.mdb"
    $SQL="SELECT * INTO CusCopy FROM Customers"
    $cmd = New-Object system.Data.OleDb.OleDbCommand($SQL,$CS)
    $cmd.Connection.Open()
    $cmd.ExecuteNonQuery()
    91 

Please take the "91" as evidence that the command affected==inserted the 91
customers from the original table to the copy.

    $SQL="SELECT * INTO [customer.csv] IN '' 'text;HDR=YES;Database=<SOMEWHERE>' FROM Customers"
    $cmd = New-Object system.Data.OleDb.OleDbCommand($SQL,$CS)
    $cmd.Connection.Open()
    $cmd.ExecuteNonQuery()
    91
    dir
    Directory: <SOMEWHERE>
    Mode                LastWriteTime     Length Name
    ----                -------------     ------ ----
    -a---        14.02.2011     22:09      13395 customer.csv
    -a---        14.02.2011     22:01    2576384 Nwind.mdb
    -a---        14.02.2011     22:09        394 schema.ini

A table .csv and a schema.ini (entry) were newly/dutifully created.