create table variable dynamically

Question

Post reply

create table variable dynamically

gsd1

Default port

Points: 1442
More actions
October 29, 2012 at 1:18 pm

#266277

Hello Friends.
What is the best way of creating table variable dynamically?
Thanks & Regards,

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

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

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?

gsd1 Default port Points: 1442 More actions · Answer 2

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,

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

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/

BriPan SSCrazy Points: 2061 More actions · Answer 4

To create Dynamic variable table

declare @temptable table

(

id int identity(1,1),

name varchar(50)

)

insert into @temptable

select name from yourtable

demonfox SSCertifiable Points: 6289 More actions · Answer 5

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:

gsd1 Default port Points: 1442 More actions · Answer 6

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,

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

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/

demonfox SSCertifiable Points: 6289 More actions · Answer 8

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: