Bulk copy with dynamically created temporary table in ADO.NET

Question

I need to create a temp table through ADO.NET, do BulkCopy and then Merge on the server between temp and the actual table.

The problem is creating a dynamic temp table using pure ADO.NET. The schema should be the same as the existing table, but this table is created using ORM (NHibernate or Entity Framework, we are not sure yet). It also means that the pattern may change in the future.

Is there a way to create a table in a database using simple ADO.NET objects? For example, a DataTable containing the schema of the source table?

All information pointing to me in the right direction is welcome.

Answer 1

I was able to create a temporary table based on an existing schema.

Blocked solution on my site .

Answer 2

You can create a temporary table using select in #somename.

 connection_ = New SqlClient.SqlConnection(connection_string_) connection_.Open() If connection_.State = ConnectionState.Open Then command_.Connection = connection_ command_.CommandType = CommandType.Text command_.CommandText = "select * into #some_table from some_table where some_id = 0" command_.ExecuteNonQuery() Dim line_index_ As Integer = 0 Dim data_table_ As DataTable = New DataTable() Using parser_ As FileIO.TextFieldParser = New FileIO.TextFieldParser(path_) parser_.SetDelimiters(delimiter_) parser_.HasFieldsEnclosedInQuotes = False While Not parser_.EndOfData If line_index_ = 0 Then Dim headers_ As String() = parser_.ReadFields() For Each header_ In headers_ data_table_.Columns.Add(header_) Next Else Dim row_ As DataRow = data_table_.NewRow() row_.ItemArray = parser_.ReadFields() data_table_.Rows.Add(row_) End If line_index_ += 1 End While End Using Using bulkCopy_ As SqlBulkCopy = New SqlBulkCopy(connection_) bulkCopy_.DestinationTableName = "#some_table" bulkCopy_.WriteToServer(data_table_) End Using ' proof command_.CommandText = "select * from #some_table" Dim reader_ As SqlDataReader = Nothing reader_ = command_.ExecuteReader line_index_ = 0 While reader_.Read line_index_ += 0 End While End If