Retrieve data from temporary table with dynamic name?
-
Hello!!!
I have a problem and need any help from you....
I've already created some temporary tables with name are created by info of user who is login windows. Then , I insert data by run exec() function ... Here my code :
declare @user1 varchar(50)
set @user1 = user_name()
-- insert data into table
declare @sql nvarchar(4000)
set @sql = 'select * into [##'+ @user1 + '] from SomeTable'
exec sp_executesql @sql
Assuming that the statement insert exec success... and because i don't have table name static so how could i retrieved data from temporary table that without use Exec() command. I can only retrieve data through run fun exec()
declare @s-2 varchar(100)
set @s-2 = 'select * from [##' + @user1 + ']'
exec (@s)
Any idea please let me know. Thanks
-
So you have several temp tables, all with the same structure, but each named according to a user in the database? My advice would be to normalise your design so that you have just one table. If you add an extra column UserName then you will know which user inserted the data:
INSERT INTO ##NormalisedTable
(SELECT user_name() as UserName, * FROM SomeTable)
This way, you're not using dynamic SQL and the EXEC command at all.
I would also recommend specifying the column list instead of using SELECT *. Why are you using temp tables instead of permanent tables in the database?
John
-
I think I'd use a permanent working table before I used a Global Temp table...
Sol, the confusing part of this whole question is... don't you know each user can have the same named temp table without it being a Global Temp table and without a clash in naming?
Open a window in Query Analyzer and run this...
CREATE TABLE #MyHead (RowNum INT IDENTITY(1,1), SomeString VARCHAR(50))
INSERT INTO #MyHead (SomeString)
SELECT 'This is from the 1st window.' UNION ALL
SELECT 'This is from the 1st window, too.'
SELECT * FROM #MyHead
Open another window in Query Analyzer and run this...
CREATE TABLE #MyHead (RowNum INT IDENTITY(1,1), SomeString VARCHAR(50))
INSERT INTO #MyHead (SomeString)
SELECT 'This is from the 2nd window.' UNION ALL
SELECT 'This is from the 2nd window, too.'
SELECT * FROM #MyHead
You may run either SELECT any time and the two tables retain separate personalities... run this and expand the "Name" column in the result and you'll find out why...
SELECT * FROM TempDB.dbo.SysObjects
WHERE Name LIKE '%MyHead%'
Temp tables of the same name all have the same name except for the last characters of the name (after all the underscores). Those last characters are automatically formed like a timestamp datatype is formed (see @@DBTS in Books Online).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for your advise.
I've already try and seen what you mentioned early.
So , althought, two temporary table have the same name( with differ owner prefix) its name in tempdb not the same.
Please correct me if wrong.
So, my next question is : how can i use variable which store name of a table and use it follow "From clause" without use EXECUTE function, like this:
declare @a varchar(20)
set @a= 'TableName'
select * from @a
Regards.
-
Almost correct... You don't need to do a different owner prefix... temp tables have scope by session only. You do not need to use a dynamic table name if the table is a temp table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
About dynamic table name , could you tell me how to use varibles which contents table name follow From clause like i said in my previous post.
Regards
-
I think you're making a big design mistake but it's your funeral...
- CREATE
PROCEDURE dbo.GenericSelect
- --===== Define I/O parameters
@pMyTable SYSNAME
- AS
- --===== Define local variables
- DECLARE
@SQL VARCHAR(8000)
- --===== Create the dynamic SQL using the input parameters
SET @SQL = '
SELECT *
FROM ' + @pMyTable
- --===== Execute the dynamic SQL
EXEC (@SQL)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
There can be no ideas about this. This is impossible in SQL Server. You have to use name of the table directly in your code. If you want to have it dynamic, you MUST resort to dynamic SQL (which means either EXECUTE(@string) or using sp_executesql procedure... well, or compose the SQL in some application like VB before you send it to SQL Server.
It is the same with column names - no parameters allowed.
The general idea is, that with a correct DB design you should always know the name of table or column you want to use. If not, then there is something wrong with design - or it is some special situation, which can then be solved using dynamic SQL.
-
Have you think of using table variable like
declare @temptable table( name varchar(20), id int)
insert into @temptable
select name, id
from tables
select * from @table
This is all memory based and table is dropped after the execution of process.
-
I think you mean SELECT * FROM @TempTable
... but you can't do this - it's invalid syntax. You may not use a variable for the table name.
To reemphasise other posters, it is difficult to see any justification for this architecture. It will be easier and more effcient to use a permanent table with a user id field. This will give the same functionality, simpler, more robust code and an audit trail.
-
You can use table as variable in SQL2000. We are using it for memory based tables.
Just search on google.
-
You mean table variables - sorry, i was thinking of conventional temporary tables - that's a different thing.
I still would recommend permanent tables. About the only benefit of table variables is that they don't hit tempdb and they have the big disadvantage that they restrict the query optimizer - they can't participate in parallel plans. As others have said, the approach that you are taking looks as if you are creating problems for yourself. Why do you want a table per user?
-
Problem is that you still need to know the name of your table variable, you can't pass it in as a parameter... and that is what sol was asking. I suppose he wanted to create the table name dynamically (like e.g. TBL+username+date) in a parameter and then use this parameter instead of table name when selecting. As was said several times this
a) does not work and
b) is a bad idea anyway.
Example - you can't do this:
DECLARE @mytable TABLE (tid INT)
DECLARE @dummy VARCHAR(20)
SET @dummy = '@mytable'
SELECT * FROM @dummy
Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@dummy'.
-
DECLARE @dummy TABLE (tid INT, VARCHAR(20))
SELECT * FROM @dummy
is this help
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply