Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

create table variable dynamically Expand / Collapse
Author
Message
Posted Monday, October 29, 2012 1:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 10:05 AM
Points: 34, Visits: 90
Hello Friends.

What is the best way of creating table variable dynamically?

Thanks & Regards,
Post #1378434
Posted Monday, October 29, 2012 1:21 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, October 24, 2014 9:55 AM
Points: 1,485, Visits: 1,036
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?
Post #1378435
Posted Monday, October 29, 2012 1:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 10:05 AM
Points: 34, Visits: 90
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,
Post #1378437
Posted Monday, October 29, 2012 1:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1378439
Posted Monday, October 29, 2012 10:56 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
To create Dynamic variable table

declare @temptable table
(
id int identity(1,1),
name varchar(50)
)

insert into @temptable
select name from yourtable
Post #1378567
Posted Tuesday, October 30, 2012 3:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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...

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
Post #1378630
Posted Tuesday, October 30, 2012 6:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 10:05 AM
Points: 34, Visits: 90
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,
Post #1378737
Posted Tuesday, October 30, 2012 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1378785
Posted Tuesday, October 30, 2012 8:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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
Post #1378804
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse