Select into table command not working in C#

Question

So I've been executing this in a C# form:

        SqlConnection sqlConnection1 = common.DBConnect();
        SqlCommand cmd = new SqlCommand();
        Int32 rowsAffected;

        cmd.CommandText = "Select * into #temp1 from web.web_entry";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = sqlConnection1;

        sqlConnection1.Open();

        rowsAffected = cmd.ExecuteNonQuery();
        sqlConnection1.Close();

The problem is this command was executed no errors or whatsoever, but when I tried to select from this temporary table in SQL server, this table isn't existing.

Temporary tables are visible only to the connection that creates it, and are deleted when the connection is closed. http://stackoverflow.com/questions/2920836/local-and-global-temporary-tables-in-sql-server — Priyank, Apr 22 '15 at 06:39
so what I'll do is I will not close the connection until I have made a select query out of it? — Eraniichan, Apr 22 '15 at 06:40
You should use stored procedure. Inside stored procedure you can create temp table and use that temp table for select. That is how it is done in general practice. — Priyank, Apr 22 '15 at 06:45
Why you need that Temp table outside the application? As @Priyank suggested you have to use Stored Procedure for selecting temp table data after the insert statement. — SelvaS, Apr 22 '15 at 06:48
Actually there are conditions set to the real query that I'm executing, and I want to create a temporary table for that so in my second query I can do a filtering of records and such. — Eraniichan, Apr 22 '15 at 06:51

score 2 · Answer 1 · answered Apr 22 '15 at 06:58

2

Temp tables will be removed after the session witch create them ends so when you close the connection sqlserver drops them

answered Apr 22 '15 at 06:58

Hossein Salmanian

753
4
14

1

yah, I didn't notice that while I was executing my query, so everytime that it runs, it was gone before I execute my query for selecting records out of it. – Eraniichan Apr 22 '15 at 07:01

score 1 · Accepted Answer · answered Apr 22 '15 at 06:54

1

So I have figured it out. What I did was I executed both queries before I close the connection and then return a datatable out of it so I can see the records retrieved.

answered Apr 22 '15 at 06:54

Eraniichan

121
1
10

Select into table command not working in C#

2 Answers2