0

I am working on a task where i had to enhance the performance of the stored procedure. However, i had stucked in an issue is that i am unable to use SELECT INTO in a conditional statement.

I had gone through some links like INSERT INTO vs SELECT INTO and sql server conditional select into temp table.

The query is as below:

IF(@abc = 7)
 BEGIN
  SELECT MemberID INTO #TempMember from dbo.Member WHERE ....
 END
ELSE
 BEGIN
  SELECT MemberID INTO #TempMember from dbo.Member WHERE ....
 END

The query above ends up showing "there is already an object named '#TempMember' in the database" at the ELSE statement. Worth pointing that i had not create a temp table for #TempMember and the if statement is not in a loop as well.

But just why does the output shows the #TempMember exist but we know that only one SELECT statement will be performed in the query above as it is a conditional?

The reason for me to use SELECT INTO because there will be 500,000 datas to be dealt with in this statement. And INSERT INTO will make the performance bad. I am unable to convert it to dynamic SQL as well because of the practice of my company.

Is there any way that I can do to use both SELECT INTO in the conditional or any better practice to insert data into the #TempTable for performance wise?

ysth
  • 96,171
  • 6
  • 121
  • 214
Chew Hong Yu
  • 137
  • 2
  • 8
  • 2
    Why tag `MySQL` when you are using `SQL Server`? – Squirrel Dec 22 '22 at 04:51
  • 1
    Why do you say using an insert into will cause poor performance? – Patrick Hurst Dec 22 '22 at 04:52
  • Sorry tagged wrongly – Chew Hong Yu Dec 22 '22 at 04:52
  • @PatrickHurst for my understanding, insert into mostly used when the table existed. But for my scenario, i need to store the value into a temp table, therefore insert into might not be a good option for this compare to select into. – Chew Hong Yu Dec 22 '22 at 04:54
  • 2
    Other potential dupes: [this one](https://stackoverflow.com/a/18777077/61305), [this one](https://stackoverflow.com/a/10703937/61305), [this one](https://stackoverflow.com/a/11987393/61305)... – Aaron Bertrand Dec 22 '22 at 04:56
  • I think you've been mislead. You probably should be creating your temp table ahead of your if, and then simply perform the inserts in either side of the if. If you can provide a more complete example of your actual problem you may also find a set based approach (inserting all the rows matching a condition at once) better than what appears to be a row-by-row solution – Patrick Hurst Dec 22 '22 at 04:57
  • Also, don't be discouraged by your question being closed as a duplicate, you can investigate the cited matching questions to see if they resolve your problem. If they don't, please edit your question with details to make it more distinct, and ask it be reviewed for reopening. – Patrick Hurst Dec 22 '22 at 05:05
  • @PatrickHurst would like to ask if creating a temptable beforehand and use insert into to insert the data, will this approach be better than using 2 temp tables and use select into to store value in it? Sorry if this is a silly question haha, still trying to learn what is the best practice to increase the performance of a stored procedure. Sidenote: I am a big fan of select into when it comes to temp tables after looking into this [article](https://blog.coeo.com/difference-between-select-into-and-insert-select) haha – Chew Hong Yu Dec 22 '22 at 05:33
  • The article doesn't really apply to #temp tables - TABLOCK helps on _permanent_ tables because they are visible to everyone, unlike #temp tables, which are only visible to you. You should test and compare. – Aaron Bertrand Dec 22 '22 at 23:29

0 Answers0