November 9, 2009 at 6:58 pm
I have a script that read records from another table and writes that into a temp table.
However, if I try to build a string and have that executed with EXEC then it does not work.
The process has no error message and it sound like completed successfully.
However, check the content the QAnalyzer returns object or table not found.
here is the script
if object_id('tempdb..#tempx') is not null drop table #tempx
go
select 'john' MyName into #tempx
go
select * from #tempx
returns john
-- now trying it a bit differently
if object_id('tempdb..#tempx') is not null drop table #tempx
go
declare @mystring nvarchar(32)
select @mystring = 'select ''john'' MyName into #tempx'
select * from #tempx
returns
(1 row(s) affected)
Msg 208, Level 16, State 0, Line 5
Invalid object name '#tempx'.
any ideas?
very strange..it tells me 1 row affected but no records nor the table 🙁
PS:: if I grab the statement without executing then it works.
select 'john' MyName into #tempx
The reason for doing it this way is that I would like to receive a parameter and build this string before executing it.
any help will be appreciated.
thx
JohnE
Cheers,
John Esraelo
November 9, 2009 at 7:37 pm
I did find out why this is not going to work.
The EXEC is qualified as an outside scope and therefore the temp table is not visible any longer.
Therefore, I have created another method:
select top 0 * into #tempx from tblXYZ
-- to create the temp table first and then I can use my EXEC string...but this time I will be using INSERT INTO instead of select .. into
thx
Cheers,
John Esraelo
November 11, 2009 at 4:57 am
Yes thats it. You'll need to create the temp table first. Then you can use dynamic SQL with EXEC to populate it.
November 11, 2009 at 8:16 am
good morning and welcome to sql server central.. you are going to have lots of fun here.
..
and thank you for the reply..
Cheers,
John Esraelo
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply