• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2159
  • Last Modified:

Creating a Temp Table Programmatically

I can create a temp table in Management Studio like this:

CREATE TABLE #ORDER_TEMP ([Order_ID] [int],[Customer_Number_FK] [nchar](10),[Product_ID_FK] [int])

However, using the same code from my C# app does not produce the same result.  Here's how I define the table and send the sql:

            sql = "CREATE TABLE #ORDER_TEMP (";
              sql = sql + " [Order_ID] [int] ";
            sql = sql + " ,[Customer_Number_FK] [nchar](10) ";
            sql = sql + " ,[Product_ID_FK] [int]) ";

            Program.InsertRecord(Program.wholesaleOrderingDatabase, "#ORDER_TEMP", "Order_ID,Customer_ID_FK,Product_ID_FK", "1,1," + iOrderNumber);

Here's the method that should build it:

    public static void ExecuteSQL(string connectionString, string sql)
        //Executes whatever SQL string is passed in
        int recordsReturned;

        SqlConnection thisConnection = new SqlConnection(connectionString);
        SqlCommand thisCommand = new SqlCommand(sql, thisConnection);
        recordsReturned = thisCommand.ExecuteNonQuery();


Why does this not work?  (I don't get an error message).
1 Solution
Éric MoreauSenior .Net ConsultantCommented:
Temp tables have a scope of a session. Each time you open a new connection (like you do), a new session is started with its own temp tables.

So you have some options:
-re-use the same connection instead of creating a new one each time.
-use global temp tables (##ORDER_TEMP)
-Don't use temp tables
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now