Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2005
    Posts
    5

    Unanswered: dynamic SQL Create Table

    The following dynamic SQL script works for creating a table on the fly but if I change the select @tmpTblNm = 'tmpABC' to select @tmpTblNm = '#tmpABC'
    it will not create the temp table. Can anyone help on creating a temp table dynamiclly?

    declare @tmpTblNm varchar(40),
    @str varchar(1000)

    select @tmpTblNm = 'tmpABC'
    select @str = ''

    -- Create a temp table to hold the current page of data
    -- Add an ID column to track the current row
    select @str = 'Create Table '+ @tmpTblNm +
    ' ( row_id int IDENTITY PRIMARY KEY,
    customerID int,
    customerName varchar(40),
    address1 varchar(40),
    city varchar(25) )'

    exec (@str)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem is that it did exactly what you requested, just not what you wanted.

    A temporary table lasts for the duration of its creator. For a script, that is as long as you have the session open (until you close the session). For a stored procedure, the duration is as long as the procedure runs. For dynamic SQL, the duration is the dynamic execute.

    When you build a temp table dynamically, the table exists for as long as the statement runs. You can use a global (aka ##) table, but that brings its own problems to play. You can create a table within your script or procedure, but then it isn't very dynamic.

    -PatP

Posting Permissions

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