create table variable dynamically

  • Hello Friends.

    What is the best way of creating table variable dynamically?

    Thanks & Regards,

  • I do not believe there is a way to create a table variable dynamically.

    Can you elaborate more on what problem your trying to solve?

  • Ray M,

    Thanks for the quick response...

    I don't know the column type to declare the table variable initially.

    I have to query sysColumns table to know the columns and then I have to create a temp table based on the result.

    Regards,

  • gsd1 (10/29/2012)


    Ray M,

    Thanks for the quick response...

    I don't know the column type to declare the table variable initially.

    I have to query sysColumns table to know the columns and then I have to create a temp table based on the result.

    Regards,

    Can you just use select into?

    select *

    into #MyTempTable

    from SomeExistingTable

    It will create #MyTempTable with the proper datatypes for each column.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • To create Dynamic variable table

    declare @temptable table

    (

    id int identity(1,1),

    name varchar(50)

    )

    insert into @temptable

    select name from yourtable

  • gsd1 (10/29/2012)


    Hello Friends.

    What is the best way of creating table variable dynamically?

    Thanks & Regards,

    that's a good one.. creating dynamic variable dynamically...:-P

    you might be able to create the table variable dynamically , but i don't think you would be able to use it ;

    even if you use dynamic sql to do that , i think that execution would be the life of table variable..

    for your requirement , i think this should work..

    select column_name into temptable

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Thanks for all the replies...

    As Sean Lange suggested, I tried with temp table (instead of temp variable).

    I faced these problems.

    1. I have to build the sql statement. So I am using EXEC (@DynamicSQL) statement.

    I am not able to get the data from temp table

    DECLARE @DynamicSQL varchar(max)

    set @DynamicSQL = 'select top 100 * into #SourceData from customers'

    EXEC (@DynamicSQL)

    select * from #SourceData

    2. I need to get the column names of the temp table. Below statement is not helping me either...

    select ',' + name

    FROM syscolumns

    WHERE id = object_id('#SourceData')

    order by name

    Thanks & Regards,

  • gsd1 (10/30/2012)


    Thanks for all the replies...

    As Sean Lange suggested, I tried with temp table (instead of temp variable).

    I faced these problems.

    1. I have to build the sql statement. So I am using EXEC (@DynamicSQL) statement.

    I am not able to get the data from temp table

    DECLARE @DynamicSQL varchar(max)

    set @DynamicSQL = 'select top 100 * into #SourceData from customers'

    EXEC (@DynamicSQL)

    select * from #SourceData

    2. I need to get the column names of the temp table. Below statement is not helping me either...

    select ',' + name

    FROM syscolumns

    WHERE id = object_id('#SourceData')

    order by name

    Thanks & Regards,

    Without any more details it is pretty difficult to provide any help. Take a look at the first link in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • gsd1 (10/30/2012)


    Thanks for all the replies...

    As Sean Lange suggested, I tried with temp table (instead of temp variable).

    I faced these problems.

    1. I have to build the sql statement. So I am using EXEC (@DynamicSQL) statement.

    I am not able to get the data from temp table

    DECLARE @DynamicSQL varchar(max)

    set @DynamicSQL = 'select top 100 * into #SourceData from customers'

    EXEC (@DynamicSQL)

    select * from #SourceData

    2. I need to get the column names of the temp table. Below statement is not helping me either...

    select ',' + name

    FROM syscolumns

    WHERE id = object_id('#SourceData')

    order by name

    Thanks & Regards,

    For 1. that's the life of the temps in the dynamic sql ; it all ends with execution;no temps created inside of "EXEC" lives to see another sql statement.

    For 2. what are you trying to accomplish..

    try this , I am making a guess..

    select * into #customers from dbo.customers where 1=2

    -- this creates the table structure for temptable; don't use dynamic

    -- after this what exactly do you want..

    please be specific , while posting ..

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

Viewing 9 posts - 1 through 8 (of 8 total)

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