1 people like it.

Bulk copy into table with IDENTITY column

Want to do a SqlBulkCopy into a table which has an IDENTITY column? If your DataTable has the same columns as the target (but without the ID), you can map the columns numerically, skipping the first target column.

1: 
2: 
3: 
4: 
5: 
6: 
7: 
open System
open System.Data.Sql
open System.Data.SqlClient

let SkipIdUsingMappings (dt : Data.DataTable) (bc : SqlBulkCopy) =
    for i in 0..dt.Columns.Count-1 do 
        bc.ColumnMappings.Add(i, i+1) |> ignore
namespace System
namespace System.Data
namespace System.Data.Sql
namespace System.Data.SqlClient
val SkipIdUsingMappings : dt:Data.DataTable -> bc:SqlBulkCopy -> unit

Full name: Script.SkipIdUsingMappings
val dt : Data.DataTable
Multiple items
namespace System.Data

--------------------
namespace Microsoft.FSharp.Data
Multiple items
type DataTable =
  inherit MarshalByValueComponent
  new : unit -> DataTable + 2 overloads
  member AcceptChanges : unit -> unit
  member BeginInit : unit -> unit
  member BeginLoadData : unit -> unit
  member CaseSensitive : bool with get, set
  member ChildRelations : DataRelationCollection
  member Clear : unit -> unit
  member Clone : unit -> DataTable
  member Columns : DataColumnCollection
  member Compute : expression:string * filter:string -> obj
  ...

Full name: System.Data.DataTable

--------------------
Data.DataTable() : unit
Data.DataTable(tableName: string) : unit
Data.DataTable(tableName: string, tableNamespace: string) : unit
val bc : SqlBulkCopy
Multiple items
type SqlBulkCopy =
  new : connection:SqlConnection -> SqlBulkCopy + 3 overloads
  member BatchSize : int with get, set
  member BulkCopyTimeout : int with get, set
  member Close : unit -> unit
  member ColumnMappings : SqlBulkCopyColumnMappingCollection
  member DestinationTableName : string with get, set
  member NotifyAfter : int with get, set
  member WriteToServer : reader:IDataReader -> unit + 3 overloads
  event SqlRowsCopied : SqlRowsCopiedEventHandler

Full name: System.Data.SqlClient.SqlBulkCopy

--------------------
SqlBulkCopy(connection: SqlConnection) : unit
SqlBulkCopy(connectionString: string) : unit
SqlBulkCopy(connectionString: string, copyOptions: SqlBulkCopyOptions) : unit
SqlBulkCopy(connection: SqlConnection, copyOptions: SqlBulkCopyOptions, externalTransaction: SqlTransaction) : unit
val i : int32
property Data.DataTable.Columns: Data.DataColumnCollection
property Data.InternalDataCollectionBase.Count: int
property SqlBulkCopy.ColumnMappings: SqlBulkCopyColumnMappingCollection
SqlBulkCopyColumnMappingCollection.Add(bulkCopyColumnMapping: SqlBulkCopyColumnMapping) : SqlBulkCopyColumnMapping
SqlBulkCopyColumnMappingCollection.Add(sourceColumnIndex: int, destinationColumnIndex: int) : SqlBulkCopyColumnMapping
SqlBulkCopyColumnMappingCollection.Add(sourceColumn: string, destinationColumnIndex: int) : SqlBulkCopyColumnMapping
SqlBulkCopyColumnMappingCollection.Add(sourceColumnIndex: int, destinationColumn: string) : SqlBulkCopyColumnMapping
SqlBulkCopyColumnMappingCollection.Add(sourceColumn: string, destinationColumn: string) : SqlBulkCopyColumnMapping
val ignore : value:'T -> unit

Full name: Microsoft.FSharp.Core.Operators.ignore
Raw view Test code New version

More information

Link:http://fssnip.net/e1
Posted:12 years ago
Author:Kit Eason
Tags: sql server