SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


create table variable dynamically


create table variable dynamically

Author
Message
gsd1
gsd1
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

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

What is the best way of creating table variable dynamically?

Thanks & Regards,
Ray M
Ray M
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2247 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-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26890 Visits: 17557
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.

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-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1557 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-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26890 Visits: 17557
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.

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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1557 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