I'm creating temporary table:
var sql = $@"
CREATE TABLE #tempImport (Year datetime NULL,
Spend decimal (19, 5) NULL, UserId int NULL, Product nvarchar(255) NULL, Margin decimal (19, 5) NULL,
ClientId int NOT NULL, Id int IDENTITY(1,1) NOT NULL);";
which I access then like:
var sqlConn = new SqlConnection();
sqlConn.ConnectionString = conn;
using (var connection = new SqlConnection(conn))
{
using (SqlCommand command = new SqlCommand("", connection))
{
try
{
connection.Open();
//Creating temp table on database
command.CommandText = sql;
command.ExecuteNonQuery();
//Bulk insert into temp table
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
{
bulkcopy.BulkCopyTimeout = 660;
bulkcopy.DestinationTableName = "#tempImport";
bulkcopy.WriteToServer(reader);
bulkcopy.Close();
}
// Updating destination table, and dropping temp table
command.CommandText = mergeScript;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
// Handle exception properly
}
finally
{
connection.Close();
}
}
However I get exception on bulkcopy.WriteToServer(reader);
:
System.InvalidOperationException: Cannot access destination table '#tempImport'.
---> System.Data.SqlClient.SqlException (0x80131904): Invalid object name '#tempImport'.
Why is that? I think all examples I found used it like this,
Its local temp table and SqlBulkCopy
is different connection so table is disappeared till that time. either you have to use the same connection or you can create global temp table using ##
but you have to delete it later.
User contributions licensed under CC BY-SA 3.0