-
temp table using dynamic sql?
Does TSQL limits us on creating temp table using dynamic sql? If so any workaround... Here's the sample code that doesn't let me run second exec because it looks like first exec is not able to create a temp table.
declare @str1 varchar(80),@str2 varchar(80)
set @str1='create table #tmp(col1 int)'
set @str2='insert into #tmp values (10)'
exec (@str1)
exec (@str2)
-
You have to change that to one single statement.
declare @str1 varchar(2300)
set nocount on
set @str1='begin create table #tmp(col1 int) End '
set @str1= @str1+'begin insert into #tmp values (10) end '
set @str1= @str1+'begin insert into #tmp values (110) end '
set @str1= @str1+'begin insert into #tmp values (120) end '
set @str1= @str1+'Select * from #tmp'
exec (@str1)
if you like to use the result of the dynamic query in another table then..
declare @str1 varchar(2300)
set nocount on
set @str1='begin create table #tmp(col1 int) End '
set @str1= @str1+'begin insert into #tmp values (10) end '
set @str1= @str1+'begin insert into #tmp values (110) end '
set @str1= @str1+'begin insert into #tmp values (120) end '
set @str1= @str1+'Select * from #tmp'
--exec (@str1)
Create table #mytable (col1 int)
insert #mytable exec (@Str1)
select * from #mytable
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|