Post reply

Problem facing while creating temporary table.

  • November 12, 2008 at 2:53 am

    #202618

    Hi,

    I am facing some problem with data types defined for temporary table.

    e.g.

    (Original Table)

    Table name: tblTask

    Column name: TaskTitle Varchar(200)

    I have created one temporary table, which is used in a stored procedure.

    (Temporary Table)

    Table name: TmpTable

    Column name: TempTitle Varchar(200)

    Now problem is.

    I want to change Size of column “TaskTitle” (from Original table “tblTask”) to 400.

    I forgot to change the size of column “TempTitle” (From Temporary table “TmpTable”), and while executing the stored procedure it throws an error.

    So can we set the datatype of column “TaskTitle” (from Original table “tblTask”) to column “TempTitle” (From Temporary table “TmpTable”) at runtime?

    If above thing is possible, so we will just have to change size of column in original table and do not have to worry about temporary table, temporary table will take size of column from original table.

    Can you please suggest the solution or alternative for this problem?

    Best Regards,

    Jayraj Todkar

  • November 12, 2008 at 3:11 am

    #897395

    You can use SELECT INTO to create a copy of your original table using the same datatypes.

    So in your case to create an empty table do

    SELECT TaskTitle as TempTitle

    INTO #tmpTable

    FROM tblTask

    where 1=0

    BTW it's better to create a real temporary table using # instead of a fixed table which you just give a tmp prefix.

    [font="Verdana"]Markus Bohse[/font]

  • November 12, 2008 at 4:38 am

    #897417

    Hi Markus Bohse,

    Thanks a lot for the solution. It’s a very useful solution for me.

    Just a quick query.

    Can we add additional columns like “SrNo” of integer data type and this column will be an identity column for Temp table? (This column, added in temporary table, should not be a part of original table)

    Once again Thanks for your help.

    Best Regards,

    Jayraj Todkar

  • November 12, 2008 at 6:38 am

    #897472

    Yes we can add.

    after temp table creation we have to use this query

    alter table #temp1

    add id int IDENTITY(1,1) NOT NULL

  • November 13, 2008 at 4:00 am

    #897968

    Hi Markus Bohse,

    Thanks for your help. Even this suggestion worked fine for me.

    Best Regards,

    Jayraj Todkar

  • November 14, 2008 at 8:49 am

    #898671

    Instead of using temp tables you could use a table variable to hold your data during processing. The field definitions will be within your procedure and can easily be altered.

Viewing 6 posts - 1 through 5 (of 5 total)

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