Bulk copy with dynamic created temp table in ADO.NET

Question

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

Problem is creating the temp table dynamic using pure ADO.NET. The schema should be the same as the existing table, but this table is created using an ORM (NHibernate or Entity Framework, we're not sure yet). This also means that the schema can change in the future.

Is there any way to create a table in the database using plain ADO.NET objects? Such as a DataTable containing the schema of the original table?

Any information pointing me in the right direction is appreciated.

Keith John Hutchison · Answer 1 · 2013-11-14T08:20:46.297

You can create a temporary table using select into #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

You could use TOP 0 instead of a WHERE clause to ensure you get no results. SELECT TOP 0 * INTO #TmpTable FROM SomeTable; — Robert, Aug 08 '14 at 15:45
This solution seems ideal, but it could fail depending on your constraints. For example, I have a field `creation DATETIME NOT NULL DEFAULT GETDATE()`. The temp table however keeps the `NOT NULL` but not the `DEFAULT GETDATE()`, which means I would have to account for that in my bulk insert. — atheaos, May 09 '16 at 17:28
If you keep the same session open you have access to the temporary table that was just created. You could create a stored procedure to work with that temporary table and take it from there. — Keith John Hutchison, May 10 '16 at 08:30
You could also create your temporary table first with your constraints and do an insert instead of a select into. — Keith John Hutchison, May 10 '16 at 08:32

score -1 · Accepted Answer · answered Apr 25 '13 at 08:37

-1

I managed to create a temp table based on an existing schema.

Blogged the solution on my site.

answered Apr 25 '13 at 08:37

Ronald

1,990
6
24
39

3

You could use TOP 0 instead of a WHERE clause to ensure you get no results. SELECT TOP 0 * INTO #TmpTable FROM SomeTable; – Robert Aug 08 '14 at 15:44
1

Your are iterating over the column list 3 times that could have been done in one shot. Most of your code is about to push the columns into a string builder that you are not even using anywhere. – Daniel Leiszen Oct 24 '17 at 10:44
1

@Daniel Leiszen, you're right. I'm happy to say 5 years later that I wrote bad code back then :) I hope I can keep saying it in the next 5 years about today's code :) – Ronald Jan 10 '18 at 09:45
@Ronald, Yes that's always a good thing. However, I keep a little faith that my coding skills at last reached a certain level and there are other fields I can work on :) – Daniel Leiszen Feb 14 '18 at 09:24
Link is broken.. – Markive Sep 22 '22 at 04:18

Bulk copy with dynamic created temp table in ADO.NET

2 Answers2