473,372 Members | 1,020 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

dynamically creating temp table names

Hello,
I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000.

For example:

DECLARE @l_personsUID int

select @l_personsUID = 9842

create table ##Test1table /*then the @l_personsUID */
(
resultset1 int
)

The key to the problem is that I want to use the variable
@l_personsUID to name then temp table. The name of the temp table
should be ##Test1table9842 not ##Test1table.

Thanks for you help.

Billy
Jul 20 '05 #1
5 15532

"Billy Cormic" <bi**********@hotmail.com> wrote in message
news:dd*************************@posting.google.co m...
Hello,
I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000.

For example:

DECLARE @l_personsUID int

select @l_personsUID = 9842

create table ##Test1table /*then the @l_personsUID */
(
resultset1 int
)

The key to the problem is that I want to use the variable
@l_personsUID to name then temp table. The name of the temp table
should be ##Test1table9842 not ##Test1table.

May I ask why?

You can probably do this by dynamically building the string.

But it's going to be messy.

Thanks for you help.

Billy

Jul 20 '05 #2
bi**********@hotmail.com (Billy Cormic) wrote in message news:<dd*************************@posting.google.c om>...
Hello,
I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000.

For example:

DECLARE @l_personsUID int

select @l_personsUID = 9842

create table ##Test1table /*then the @l_personsUID */
(
resultset1 int
)

The key to the problem is that I want to use the variable
@l_personsUID to name then temp table. The name of the temp table
should be ##Test1table9842 not ##Test1table.

Thanks for you help.

Billy


You could use dynamic SQL, but that would not be a good solution. If
the table names are dynamic, then all code accessing the tables would
need to be dynamic also, and that will create a lot of issues.

A better approach would be to have a single, permanent table, with
personsUID as part of the key. See here for a good discussion of this
issue:

http://www.algonet.se/~sommar/dynami...tml#Sales_yymm

Simon
Jul 20 '05 #3
I want to do this so that i can create individual tables to set as
datasources for certain crystal reports.

"Greg D. Moore \(Strider\)" <mo*****@greenms.com> wrote in message news:<2j********************@twister.nyroc.rr.com> ...
"Billy Cormic" <bi**********@hotmail.com> wrote in message
news:dd*************************@posting.google.co m...
Hello,
I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000.

For example:

DECLARE @l_personsUID int

select @l_personsUID = 9842

create table ##Test1table /*then the @l_personsUID */
(
resultset1 int
)

The key to the problem is that I want to use the variable
@l_personsUID to name then temp table. The name of the temp table
should be ##Test1table9842 not ##Test1table.


May I ask why?

You can probably do this by dynamically building the string.

But it's going to be messy.

Thanks for you help.

Billy

Jul 20 '05 #4
>> I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000. <<

Learn to write correct SQL instead. The use of temp tables is usually
a sign of really bad code -- the temp tables are almost always used to
hold steps in a procedural solution instead of a having a set-oriented
non-proceudral solution. This also says that you have no data model
and that any user, present or future, can change it on the fly.

Oh, if you don't care about performance, portability, readability,
security, and all that other stuff, then you can use dynamic SQL to
screw up your application this way.
Jul 20 '05 #5
OK. I will just create anohter table... not a bunch of temp tables to
hold the results.

thanks

jo*******@northface.edu (--CELKO--) wrote in message news:<a2**************************@posting.google. com>...
I am interested in dynamically creating temp tables using a

variable in MS SQL Server 2000. <<

Learn to write correct SQL instead. The use of temp tables is usually
a sign of really bad code -- the temp tables are almost always used to
hold steps in a procedural solution instead of a having a set-oriented
non-proceudral solution. This also says that you have no data model
and that any user, present or future, can change it on the fly.

Oh, if you don't care about performance, portability, readability,
security, and all that other stuff, then you can use dynamic SQL to
screw up your application this way.

Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Ex-Em-El | last post by:
I have a problem in creating a dynamic table in the same xml : 1.xml: <?xml version="1.0" standalone="yes"?> <?xml-stylesheet type="text/xsl" href="2.xsl"?> <aaa...
11
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the...
7
by: John Baker | last post by:
Hi: I would like to know how to create a temp DB to store the data in a table while I do something else with the table. Specifically, how do I create the temp remove the temp I want to be...
0
by: Alexandre | last post by:
hey this is the error im getting : --> Cannot widen from target type to primitive type. this line produces the error : temp = o.GetType().InvokeMember("", BindingFlags.CreateInstance, null,...
9
by: netasp | last post by:
hi all, how can I populate one aspx form when page is loading based on page ID? for example: loading page A (to search for VB code) would display labels and texboxes, dropdown lists all related...
16
by: pukivruki | last post by:
hi, I wish to create a temporary table who's name is dynamic based on the argument. ALTER PROCEDURE . @PID1 VARCHAR(50), @PID2 VARCHAR(50), @TICKET VARCHAR(20)
4
by: m.wanstall | last post by:
I have a crosstab query that compiles data for Months of the year. I have a stacked select query on top of that crosstab query that uses the latest 2 months data and exports it to a fixed length...
3
by: N L | last post by:
Greetings, I want to create a form in Access that shows a list of checkboxes to a user. The checkboxes will be dynamically generated, showing the fields in a table the user has selected in a...
1
by: semomaniz | last post by:
I have a form where i have created the form dynamically. First i manually added a panel control to the web page. Then i added another panel dynamically and inside this panel i created tables. I have...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.