September 14, 2017 at 7:45 am
How can I insert into temp table from if else statement? The reason is to do second checking after inserting into temp table.
I tried this but getting error object already exist.
select 0 as testing into #temp from tableA
if ( select * from #temp ) >0
select 'Pass' as testing into #temptable
else
select 'Fail' as testing into #temptable
September 14, 2017 at 7:54 am
girl_bj - Thursday, September 14, 2017 7:45 AMHow can I insert into temp table from if else statement? The reason is to do second checking after inserting into temp table.I tried this but getting error object already exist.
select 0 as testing into #temp from tableA
if ( select * from #temp ) >0
select 'Pass' as testing into #temptable
else
select 'Fail' as testing into #temptable
You need to create the temp table before your if condition. The sql compiler does not run your logic to determine that the temp table would only be created from one path. As such it interprets this as it being created twice which will fail. But your if condition is a little suspect also. I think you want EXISTS here because your if condition doesn't make sense.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2017 at 7:57 am
girl_bj - Thursday, September 14, 2017 7:45 AMHow can I insert into temp table from if else statement? The reason is to do second checking after inserting into temp table.I tried this but getting error object already exist.
select 0 as testing into #temp from tableA
if ( select * from #temp ) >0
select 'Pass' as testing into #temptable
else
select 'Fail' as testing into #temptable
Simply means your local temp table #temptable still exists from running the statement a previous time in the same session.
Either generate new #temptable names when executed in the same session or add a IF EXIST DROP before doing multiple similar statements in the same session.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
September 14, 2017 at 7:58 am
girl_bj - Thursday, September 14, 2017 7:45 AMHow can I insert into temp table from if else statement? The reason is to do second checking after inserting into temp table.I tried this but getting error object already exist.
select 0 as testing into #temp from tableA
if ( select * from #temp ) >0
select 'Pass' as testing into #temptable
else
select 'Fail' as testing into #temptable
Try this:select 0 as testing
into #temp
from tableA;
DECLARE @PASS_FAIL AS char(4) = (SELECT CASE WHEN (select testing from #temp) > 0 THEN 'Pass' ELSE 'Fail' END);
SELECT @PASS_FAIL AS testing
INTO #temptable;
DROP TABLE #temp;
DROP TABLE #temptable;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 14, 2017 at 8:07 am
Henrico Bekker - Thursday, September 14, 2017 7:57 AMgirl_bj - Thursday, September 14, 2017 7:45 AMHow can I insert into temp table from if else statement? The reason is to do second checking after inserting into temp table.I tried this but getting error object already exist.
select 0 as testing into #temp from tableA
if ( select * from #temp ) >0
select 'Pass' as testing into #temptable
else
select 'Fail' as testing into #temptableSimply means your local temp table #temptable still exists from running the statement a previous time in the same session.
Either generate new #temptable names when executed in the same session or add a IF EXIST DROP before doing multiple similar statements in the same session.
Nope. Copy that code and try to execute it. It will fail. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply