Cannot access Temporary table .NET Core

0

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,

c#
sql
sql-server
.net-core
azure-sql-database
asked on Stack Overflow Jun 4, 2020 by user122222 • edited Jun 4, 2020 by Pac0

1 Answer

1

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.

answered on Stack Overflow Jun 4, 2020 by vivek nuna

User contributions licensed under CC BY-SA 3.0