CODEWITHSUNDEEP
sql-serversqlbulkcopy
Maury Markowitz
Maury Markowitz

Reputation: 9283

SqlBulkCopy into a temp table

I am trying to improve performance of UPDATEs. Following advice in other threads, I am trying to use BulkInsert into a staging table and then UPDATE... WHERE to join against the production table.

The data itself is in a DataTable in VB.net, and I have used SqlBulkInsert to quickly load data from DataTables into SQL Server widely in the past. So my thought was to use a DbCommand to run:

SELECT * INTO ##Projects FROM Projects WHERE 1=0

When I run that code I can see the table is being created when I look in SSMS (which is why I'm using ##). The single DataTable entries in this case is:

Name: "test"
Address: "123 Anywhere St.
City: "St. Louis"

With that done, I tried to run:

   Using S As SqlBulkCopy = New SqlBulkCopy(ConStr)
       S.DestinationTableName = TableName
       S.WriteToServer(DT)
       S.Close()
   End Using

This fails with an error

SqlException: Invalid object name '#Projects'

After a bit of playing around with it, I replaced the ## with TEMP to make TEMPProjects and it worked perfectly. This is the same code I've used many times before.

Looking at threads here, like this one, it seems no special setup is needed for working with temp tables. I'm at a loss, did I miss something?

Upvotes: 0

Views: 78

Answers (1)

Charlieface
Charlieface

Reputation: 72128

The problem is that you are giving SqlBulkCopy the connection string, rather than an open connection, so it will open its own separate connection.

This other connection has no relation to the other connection, and therefore cannot see its local temp tables. And if you closed the previous connection then any global temp tables are dropped as well.

You need to use the same connection without closing in between. You also need to make sure that the command that creates the temp table does not use parameters, otherwise it goes via sp_executesql in a separate scope.

Using con As SqlConnection = New SqlConnection(ConStr)
    con.Open()
    Using cmd As SqlCommand = New SqlCommand(createQuery, con)
        cmd.ExecuteNonQuery()
    End Using
    Using S As SqlBulkCopy = New SqlBulkCopy(con)
        S.DestinationTableName = TableName
        S.WriteToServer(DT)
    End Using      ' no need to close if you have a Using
End Using

Upvotes: 3

Related Questions