Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


create table variable dynamically


create table variable dynamically

Author
Message
gsd1
gsd1
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 90
Hello Friends.

What is the best way of creating table variable dynamically?

Thanks & Regards,
Ray M
Ray M
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1076
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
gsd1
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
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,
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16674 Visits: 17033
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)
BriPan
BriPan
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 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
demonfox
demonfox
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1221 Visits: 1192
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
gsd1
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
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,
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16674 Visits: 17033
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)
demonfox
demonfox
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1221 Visits: 1192
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search