Hi @Light Qian , Welcome to Microsoft Q&A,
In your case, using parameterized queries to create temporary tables seems to cause execution plan optimization issues. This may be due to SQL Server being unable to correctly generate the execution plan for the SELECT INTO
statement because it has difficulty determining the parameter values.
I suggest you create a temporary table first and then use parameterized queries to insert data: First create a temporary table and then use parameterized queries to insert data into the temporary table. This is a safe approach, but requires two queries to be executed.
Similar to
string createTableStatement = "CREATE TABLE #TempTable (id INT, name NVARCHAR(100))";
string insertDataStatement = "INSERT INTO #TempTable (id, name) SELECT id, name FROM Table1 WHERE name = @p0";
// ...
using (SqlCommand createTableCmd = new SqlCommand(createTableStatement, sqlConnection))
using (SqlCommand insertDataCmd = new SqlCommand(insertDataStatement, sqlConnection))
{
createTableCmd.CommandType = CommandType.Text;
insertDataCmd.CommandType = CommandType.Text;
createTableCmd.ExecuteNonQuery();
SqlParameter sqlParameter = new SqlParameter("@p0", SqlDbType.NVarChar, 100);
sqlParameter.Value = "1";
insertDataCmd.Parameters.Add(sqlParameter);
insertDataCmd.ExecuteNonQuery();
}
Best Regards,
Jiale
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.