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

Question

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?

Chad Miller · Accepted Answer · 2011-02-15 00:00:17Z

3

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.

answered Feb 15, 2011 at 0:00

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Conrad Frix · Accepted Answer · 2011-02-14 21:18:52Z

1

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.

answered Feb 14, 2011 at 21:18

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Comments

Ekkehard.Horner · Accepted Answer · 2011-02-14 21:27:07Z

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.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related