Results 1 to 3 of 3

Thread: temp table using dynamic sql?

  1. #1
    Join Date
    Feb 2003
    Posts
    34

    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)

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  3. #3
    Join Date
    Feb 2003
    Posts
    34
    thanks a lot...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •