Post reply

Temp Table and column defination.

  • July 8, 2011 at 4:27 am

    #254207

    Hello,

    I want to use temp table but don't want to declare it statically . Here statically means I don't want to make temp table definition predefined. That may change.

    For e.g.

    Select * into #TempTableName from TableName

    Now if in future TableName gets change then automatically #TempTableName defination should change.

    But main catch is that I don't want to use "Select * into" for some reason.(I can use declare)

    May be I am too much optimistic..but really is there any alternative for this?

    Thanks in advance.

    -Mahesh. 🙂

  • July 8, 2011 at 4:48 am

    #1350238

    Mahesh

    This will provide a starting point for getting the definition of your table:

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'MyTable'

    ORDER BY ORDINAL_POSITION ASC

    You should be table to build a CREATE TABLE statement from that.

    John

  • July 8, 2011 at 5:00 am

    #1350242

    John Mitchell-245523 (7/8/2011)

    ... You should be table to build ...

    professional dyslexic :hehe:

    Happens to me all the time :blush:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data and code to get the best help

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • July 8, 2011 at 5:05 am

    #1350244

    Damn! I always try to remember to read through before posting, but usually fail.

    John

  • July 8, 2011 at 5:19 am

    #1350252

    Just remember the scoping rules. If a temp table is created in dynamic SQL, it's only visible within that dynamic SQL and will be automatically dropped once that piece of dynamic SQL ends.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 8, 2011 at 5:45 am

    #1350268

    John Mitchell-245523 and ALZDBA 😛

    Thnx GilaMonster.

    Fetching column definition and creating temp table may be the solution , but is there any simple solution?

    Too much lazy..humm ?? 😎

  • July 8, 2011 at 5:52 am

    #1350273

    SELECT ... INTO is the 'simple' solution.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply