locked
How to automatically map columns from DataTable to a SQL Table with BulkCopy? RRS feed

  • Question

  • Suppose I have a SQL Table that has these columns:

    [server_name],[SESSION_ID],[SESSION_SPID]

    I am trying to copy values stored in a data table ($dmvResult) to the SQL Table above ($Table)

    $dmvResult = DMV_Query 'SELECT [SESSION_ID]
          ,[SESSION_SPID]
    FROM $SYSTEM.DISCOVER_SESSIONS';
    
    $ConnectionString ='Data Source=$server; Database=$database; Trusted_Connection=True;'
    $bulkCopy = new-object Data.SqlClient.SqlBulkCopy($ConnectionString)
    $bulkCopy.DestinationTableName=$Table
    $bulkCopy.WriteToServer($dmvResult)

    While the copying is being done successfully, there is an issue: it's copying by position, not by column name match. In other words, the copied columns are not being mapped and copied to the same columns.

    [SESSION_ID] is being copied to [server_name] and [SESSION_SPID] is being copied to [SESSION_ID]

    How can I tell bulkCopy to match columns and copy?

    The result copy should be [server_name] being empty because it wasn't selected from DMV query.

    I found a neat solution in this thread:

    https://stackoverflow.com/a/20045505/8397835

    but I dont know how to translate it to my powershell code:

    var meta = definition.Context.Mapping.GetMetaType(typeof(T));
            foreach (var col in meta.DataMembers)
            {
                copy.ColumnMappings.Add(col.Member.Name, col.MappedName);
            }
    Thursday, January 31, 2019 9:14 PM

Answers

  • In the last line of your function change to this:

    ,$dataSet.Tables[0]

    Note the added comma.

    or just return the dataset and do this:

    $dmvResult.Tables[0] | select columnname


    \_(ツ)_/

    • Marked as answer by cataster Friday, February 1, 2019 1:07 AM
    Friday, February 1, 2019 12:36 AM

All replies

  • You can figure that out

    $bulkCopy.CollumnMappings.Add('name1','name2')


    \_(ツ)_/


    • Edited by jrv Thursday, January 31, 2019 10:43 PM
    Thursday, January 31, 2019 10:43 PM
  • You can figure that out

    $bulkCopy.CollumnMappings.Add('name1','name2')


    \_(ツ)_/


    yes this means i have to specify the columns, thus its not automatic. i want the bulkcopy to automatically know which column goes where with simply matching the name from the DataTable ($dmvResult)
    Thursday, January 31, 2019 10:50 PM
  • If the column names are not the same how would it know.  The query must return column names that match  thhe target table columns and it will be automatic.


    \_(ツ)_/

    Thursday, January 31, 2019 10:55 PM
  • If the column names are not the same how would it know.  The query must return column names that match  thhe target table columns and it will be automatic.


    \_(ツ)_/

    but the column names are the same...thats what im saying, since they are the same, there should be a way to just say something like: 

    foreach ($column in $dmvResult.Columns) { $bulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName) }

    but this doesnt work

    Exception calling "WriteToServer" with "1" argument(s): "The given ColumnMapping does not match up with any column in the source or destination." –

    Thursday, January 31, 2019 11:09 PM
  • If the column names are exactly the same then the mappings are automatic.  The code I gave you does that automatically.


    \_(ツ)_/


    • Edited by jrv Thursday, January 31, 2019 11:10 PM
    Thursday, January 31, 2019 11:10 PM
  • If the column names are exactly the same then the mappings are automatic.  The code I gave you does that automatically.


    \_(ツ)_/


    they are the same, but it still copied them based on position, not name matching. and hence, it copied [session_ID] into the first column in sql table [server_name] which is not right. it should have copied it into the second column [session_ID]
    Thursday, January 31, 2019 11:17 PM
  • Then look closely at the data table column names to be sure they are what you want.  

    $dt.Columns |%{$_.Name}


    \_(ツ)_/

    Thursday, January 31, 2019 11:20 PM
  • Then look closely at the data table column names to be sure they are what you want.  

    $dt.Columns |%{$_.Name}


    \_(ツ)_/

    doesnt output anything
    Thursday, January 31, 2019 11:25 PM
  • Then your data table is either empty or doesn't exist.


    \_(ツ)_/

    Thursday, January 31, 2019 11:32 PM
  • Then your data table is either empty or doesn't exist.


    \_(ツ)_/

    ok so i tried this

    $bulkCopy.ColumnMappings.Add('SESSION_ID', 'SESSION_ID')

    $bulkCopy.ColumnMappings.Add('SESSION_SPID', 'SESSION_SPID') $bulkCopy.WriteToServer($dmvResult)


    and it mapped to the correct columns. so it cant be that the data table ($dmvResult) is empty, since it IS copying data. 

    however, it outputted this to the console as well:

    

    also, its supposed ot overwrite the data in the sql table...not append extra rows...can i suppress this appending and instead overwrite?

    Thursday, January 31, 2019 11:39 PM
  • Sorry.  It should be:

    $dt.Columns | %{$_.ColumnName}


    \_(ツ)_/

    Thursday, January 31, 2019 11:47 PM
  • Sorry.  It should be:

    $dt.Columns | %{$_.ColumnName}


    \_(ツ)_/

    still doesnt output anything
    Thursday, January 31, 2019 11:49 PM
  • You asked how to "insert" Now you are asking for more.  Just connect to the server and "truncate" the table before you copy.


    \_(ツ)_/

    Thursday, January 31, 2019 11:51 PM
  • You asked how to "insert" Now you are asking for more.  Just connect to the server and "truncate" the table before you copy.


    \_(ツ)_/

    oh forget the appending. i am still talking about the main issue here of mapping automatically :)
    Thursday, January 31, 2019 11:53 PM
  • Sorry  but you will have to do some analysis and debugging to figure out how this works.  We cannot run your code and there is no way to see what you are doing.


    \_(ツ)_/

    Friday, February 1, 2019 12:04 AM
  • Also you need to use the table you created:

    $dmvResult.Columns | select ColumnName


    \_(ツ)_/

    Friday, February 1, 2019 12:07 AM
  • Also you need to use the table you created:

    $dmvResult.Columns | select ColumnName


    \_(ツ)_/

    Of course, thts what I made sure I have been using but still no output 
    Friday, February 1, 2019 12:15 AM
  • In the last line of your function change to this:

    ,$dataSet.Tables[0]

    Note the added comma.

    or just return the dataset and do this:

    $dmvResult.Tables[0] | select columnname


    \_(ツ)_/

    • Marked as answer by cataster Friday, February 1, 2019 1:07 AM
    Friday, February 1, 2019 12:36 AM
  • In the last line of your function change to this:

    ,$dataSet.Tables[0]

    Note the added comma.

    or just return the dataset and do this:

    $dmvResult.Tables[0] | select columnname


    \_(ツ)_/

    ok so i added the comma and $dmvResult.Columns | select columnname returns this:

    Friday, February 1, 2019 12:44 AM
  • Works fine for me.

    Are the datatypes different?


    \_(ツ)_/

    Friday, February 1, 2019 12:54 AM
  • Works fine for me.

    Are the datatypes different?


    \_(ツ)_/

    in the sql table they are varchar. so they are string
    Friday, February 1, 2019 12:57 AM
  • Works fine for me.

    Are the datatypes different?


    \_(ツ)_/

    in the sql table they are varchar. so they are string

    OMG! I just tried the foreach loop with the comma you told me to add to the function. 

    foreach ($column in $dmvResult.Columns) { $bulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName) > $null }
    
    $bulkCopy.WriteToServer($dmvResult)

    the table now populates correctly!!!!!

    WHY?? why did it take a simple comma to fix this????

    what is that comma even?

    ,$dataSet.Tables[0]

    Friday, February 1, 2019 1:06 AM
  • It is a compensation for the fact that a collection is auto-enumerated in PowerShell which causes the Rows to be removed from the table and turned into an array wrapped collection of rows.


    \_(ツ)_/

    Friday, February 1, 2019 1:35 AM
  • It is a compensation for the fact that a collection is auto-enumerated in PowerShell which causes the Rows to be removed from the table and turned into an array wrapped collection of rows.


    \_(ツ)_/

    Is there some documentation about this?
    Friday, February 1, 2019 1:40 AM
  • Basic PowerShell behavior.  Some objects are treated like simple collections.  I know of no documentation addressing the issue it is just something you have to know about PowerShell.

    It affects many object types that have certain derivations. 


    \_(ツ)_/

    Friday, February 1, 2019 2:33 AM
  • Here is an example of a faster way to do this.

    Function BulkCopy-SqlQueryToTable {
        Param (
            [Parameter(Mandatory=$true)]
            [string]$Sql,
            [Parameter(Mandatory = $true)]
            [string]$SourceConnectionString,
            [Parameter(Mandatory = $true)]
            [string]$TargerConnectionString,
            [Parameter(Mandatory = $true)]
            [string] $TargetTable,
            [string[]]$Mappings
        )
        
        Try{
            $conn = New-Object System.Data.SqlClient.SqlConnection($SourceConnectionString)
            $conn.Open()
            $cmd = $conn.CreateCommand()
            $cmd.CommandText = $Sql
    
            $rdr = $cmd.ExecuteReader()
            
            $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($TargerConnectionString)
            $Mappings | %{ [void]$bulkCopy.ColumnMappings.Add($_,$_)}
            $bulkCopy.DestinationTableName = $TargetTable
            $bulkCopy.WriteToServer($rdr)
        }
        Catch{
            Throw
        }
        Finally{
            $conn.Close()
        }
    }
    
    $splat = @{
    SourceConnectionString ='Integrated Security=SSPI;Initial Catalog=master;Data Source=alpha'
    TargerConnectionString = 'Integrated Security=SSPI;Initial Catalog=TestDB;Data Source=alpha'
    Sql = 'select name, xtype from syscolumns'
    TargetTable = 'Mytable'
    Mappings = 'Name', 'xType'
    }
    
    BulkCopy-SqlQueryToTable @splat
    



    \_(ツ)_/




    • Edited by jrv Friday, February 1, 2019 3:25 AM
    Friday, February 1, 2019 3:03 AM