Is there a way to put a variable name into the name of a temporary table. For example, I would like to create a table called 'test@passedvariable', or test2 assuming that @passedvariable is 2. How would I go about doing this. Any help would be greatly appreciated.
Thanks,
Jon
Hi all,
I had a problem in a stored proc when I was using table variables in SQL Server 2005. I fixed the problem by changing them to temporary tables. It works now but I really want to understand why I had to do what I did. Here's the situation:
Had a stored proc that cached quite a bit of data (100-200k records with 7 columns) into a table variable. Then, it looped through this and returned a result set. The table this procedure was querying, then caching, was highly transactional. After about 2 or 3 users were hitting the same area at once, the procedure locked. If there were no locks, it executed very fast. However, it usually timed out (due to this locking).
After researching, I see that your usually better off sticking with temp tables for large amounts of data - but the primary explanation I hear for this is that you can index it. However, it also seems that the temp table will perform read-locking as its an actual physical table, whereas the table variable will not. Therefore, because there's no locking and its entirely in memory, the table variable is often the better choice. This is confusing me though because the problem I was experiencing was some sort of locking - which I thought would have been less likely with table variables.
The only other thought I had is that the table variables (if there were several of them existing at once due to several users executing the same procedure), were causing some kind of memory limit to be hit, making the database wait until some more memory became available.
Sorry for the long post, but I'd really like to know if anyone else has had these issues and what the cause might be.
Thanks,
Mike
Hi
I am running SQL Server 2000 and have two different databases with different names on two different machines.
The servers (machines) are linked and I want to use a single set of stored procedures on both databases.
I want to know if and how I can use variables in the four naming conventions in a stored procedure. This way I do not have to write an if statement and rewrite the code for each database.
Example:
Select * from SERVER_A.DATABASE_1.dbo.CENSUS
Eventually, I want SERVER_A and DATABASE_1 to become the variables that will change depending on which database that is being requested.
Any help would be appreciated.
Is there a good way to name tables that are used as lookup tables? If you have a table for movies and you think that a movie can be in more than one genre. Then you will have tables like the following.
a table for movies, table 1
id name ...
1 Scarface
a table for the movie genres, table 2
id name ...
1 action
2 mafia
a table that links the genres to the movies, table 3
id table 1 id table 2 id
1 1 1
2 1 2
Table 1 is no problem. I name it movie.
Table 2 is the genres. I name it movie_genres.
Table 3 specifies the genres for a specific movie so I name it movie_genres. Duh, already taken.
This is just an example and perhaps not the best. What should I call my to genre tables?
Firstly I consider myself quite an experienced SQL Server user, andamnow using SQL Server 2005 Express for the main backend of mysoftware.My problem is thus: The boss needs to run reports; I have designedthese reports as SQL procedures, to be executed through an ASPapplication. Basic, and even medium sized (10,000+ records) reportingrun at an acceptable speed, but for anything larger, IIS timeouts andquery timeouts often cause problems.I subsequently came up with the idea that I could reduce processingtimes by up to two-thirds by writing information from eachcalculationstage to a number of tables as the reporting procedure runs..ie. stage 1, write to table xxx1,stage 2 reads table xxx1 and writes to table xxx2,stage 3 reads table xxx2 and writes to table xxx3,etc, etc, etcprocedure read final table, and outputs information.This works wonderfully, EXCEPT that two people can't run the samereport at the same time, because as one procedure creates and writesto table xxx2, the other procedure tries to drop the table, or read atable that has already been dropped....Does anyone have any suggestions about how to get around thisproblem?I have thought about generating the table names dynamically using'sp_execute', but the statement I need to run is far too long(apparently there is a maximum length you can pass to it), and evenbreaking it down into sub-procedures is soooooooooooooooo timeconsuming and inefficient having to format statements as strings(replacing quotes and so on)How can I use multiple tables, or indeed process HUGE procedures,withdynamic table names, or temporary tables?All answers/suggestions/questions gratefully received.Thanks
View 2 Replies View RelatedI did a search (google and on the forums) and found a few suggestions here and there, but I'd like something more complete to follow as far as naming conventions are concerned.
I wrote my first DB based on MySQL/Ruby/Active Record type naming convention...
- plural table names
- all lower cased
- underscores between words
- "id" is auto incrementer for each table
- something+"_at" is for datetime fields
- something+"_on" is for date fields
- referencing the primary id in another table is "tablename (singular)" + "_id".
This worked great in Ruby/MySQL, but in C#/SQL Server, its an ambiguity nightmare! All of my "id" fields conflict and alot of my tables have "added_at" datetime fields and they all conflict with each other. Essentially, any field that's named the same in one table as in another conflict on joins.
For example: users post comments to stories submitted by users...
table = articles
field 1 = id
field 2 = title
field 3 = body
field 4 = user_id
table = comments
field 1 = id
field 2 = title
field 3 = body
field 4 = user_id
field 5 = article_id
Trying to join these two tables is an ambiguity nightmare but I'd like to not have to name every field uniquely or start adding table prefixes to them all...
I guess I just need some good suggestions or links to recommended table structure/naming conventions for SQL Server. Thanks in advance!
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
Can you create a temporary table using an ad-hoc query?
What I am trying to do is a type of filter search (the user has this and this or this) were i will not know how may items the user is going to select until they submit....that is why i can't use a stored procedure(i think)....any help on how to do this?
Thanks,
Trey
Hi could anyone give me a hint what does term "temporary table" mean regarding sql server?
View 1 Replies View RelatedHi all,
how can i execute this query without errors??
create table #luca(c int)
drop table #luca
select * into #luca
from anagrafica_clienti
The error lanched is:
Server: Msg 2714, Level 16, State 1, Line 6
There is already an object named '#luca' in the database.
Why if i drop the table?How can i do?Thanks guy
Afternoon.
I'm having trouble with a query and ASP. The query itself is easy. I need a temporary table to be filled with the contents of a select and then i need to select out of the temporary table and return the results to the ASP.
So:
Code:
DECLARE @RESULTS TABLE (
ItemID int,
ItemDescription char(50),
ItemType int,
ItemRequestedBy char(50),
ItemStatus int,
ItemQuantity int,
ItemCostPer money,
ItemOrderNumber int,
DateAdded smalldatetime,
DateLastEdited smallDateTime
)
INSERT into @results (ItemID, ItemDescription, ItemType, ItemRequestedBy, ItemStatus, ItemQuantity, ItemCostPer, ItemOrderNumber, DateAdded, DateLastEdited)
SELECT * from cr_EquipmentData
SELECT * from @results
When I run this in Query Analyser, I get exactly the results I need... But when I try and run the ASP script, I get an error about the recordset not being open. (It's not the ASP checking 'cos when I run a simple select statement it works)
I appreciate there is no use for the query as it stands, but eventually I want to be able to perform not destructive statements on the @results table before returning the data to ASP. This is more 'testing' than anything at the mo'
Has anyone got any ideas?
Thanks...
I am writing a stored procedure that outputs it's information to a temporary table while it assembles the information. Afterwards, it returns the contents of the temporary table as the results of the stored procedure.
I found that if you create the table, inside the SP, as an ordinary table, the information builds to that table considerably faster than if you use a true temporary table.
I found that if I create a user function that returns a table as it's return value, it is also as slow as if I used a true temporary table.
The database can amass over 2 million records in one table in just a few days. If I have the procedure query against this table, and output to an ordinary table it creates, and summarize the information it is adding to the table, then it takes an average of around 4 minutes to return the results from the query. If I change the output table to a temporary table (#temp), it between 12 and 15 minutes. Nothing else in the procedure changed. Just the kind of table. If I take the logic and move it to a function which returns those results in a RETURN table, it also takes over 14 minutes.
Why would it take so much longer outputing to a temporary table rather than a normal table? Is it because temporary tables are stored in a different database (tempdb)? Why would returning query results from a function be just as slow?
How can I view logs of local (to a session) temporary that are created/dropped?
View 6 Replies View RelatedHi,
I am trying to join two tables usig two temporary tables.I want the Output as table2/table1 = Output
Select temp2.Value/temp1.Value as val
from
(
(
select count(*)as Value from [Master] m
inner join [Spares]s on s.SId=m.SID
where m.Valid_From between '2007-06-01' and '2007-06-30'
)temp1
Union
(
select isnull(sum(convert(numeric(10,0),s.Unit_Price)),'0') as Value
from [Order] h
inner join [Spares] s on s.Number = s.Number
where h.Valid_Date between '2007-06-01' and '2007-06-30'
))temp2
as t
I could not find the output..
Plz help me..
Thanks..
Hello,
Our java application is running on oracle and now we would like to port it to sql server 2000. In oracle we have a global temporary tables that has an option on commit to delete rows.
Now I am looking for the same option in sql server but as far as I can see sql server's local temporary tables are visible per connection. Since the connection are shared in the pool it seems I can not rely on local temporary tables since the connection does not get closed at the end of the user's session. I need something that is visible per transaction not per connection.
Is it a better approach just to create a regular table and basically have a trigger to delete all data on commit?
If a stored procedure invokes another stored procedure that creates atemporary table why can't the calling procedure see the temporary table?CREATE PROCEDURE dbo.GetTempASCREATE TABLE #Test([id] int not null identity,[name] as char(4))INSERT INTO #Test ([name]) VALUES ('Test')CREATE PROCEDURE dbo.TestASEXEC dbo.GetTempSELECT * FROM #Test -- Invalid object name '#Test'.Thanks,TP
View 4 Replies View RelatedHi, just a quick question regarding performance and speed.Q. Run a complicated query three times or create a TEMPORARY table andaccess it as needed?I have a page where it will be accessed 10,000+ a day.In that page I have an SQL query where it involves 3 different tables(approximate table sizes T1) 200,000 T2) 900,000 T3) 20 records)I'll be running that query 3 times in that page...One to retrieve the content and display it on the page.Second to count the number of records (using COUNT(*) function)And third to retrieve the content regions. (using DISTINCT function)What would be the best way of doing...Running the SQL query 3 timesOrCreate a temporary table and access it as many time as I needRegards,
View 4 Replies View RelatedHello,
I would like to know from other members whether they are successful in using temporary tables within a stored procedure and use that stored procedure in a report.
My scenario is like this:
I have a stored procedure A which fetches the data from different tables based on the orderno passed as input parameter.
I have built another stored procedure B with a temporary table created and inserting the rows in to this temporary table based on vendor related specifc orders by calling the above procedure A.
I have used this stored procedure in the dataset created and getting this error:
Could not generate a list of fields for the query. Check the query syntax, or click the Refresh Fields on the query toolbar.
Does anybody encountered this and have a resolution.
Thanks in advance.
Hello,
View 6 Replies View RelatedHi All
declare @temp table
([EVENT_TYPE_ID] [int],
[Desc] [nchar](50) NOT NULL,
[Lead_Time] [smallint] NULL)
INSERT @temp
SELECT *
FROM TBL_EVENT_DEFINiTION
The table definition for @temp is the same as TBL_EVENT_DEFINiTION. The problem is I need to add a field to the @temp table and define the values using an update statement. If I include the additional field in the @temp table declaration then I get an error saying something to the effect of the number of fields is different. Is there a way of altering the temporary table to add this field?
Many thanks in advance
Alex
Hello!
I'm creating a temporary table in a procedure to help me with some calculations. I would like the name of the temporary table to be 'dynamic', I mean, to have the hour it was created, something like this: create table #myTempTable15May11:10:00, so if someone access the procedure while it's running, he won't have problems in creating his 'own' temporary table (create table #myTempTable15May11:10:02). Is there anyway I can do this?
Thank you!
Hello,
I tried to make a DTS to transform data in a text file, I used a Store Procedure that use a temp table (#Resultados) but the DTS give me an error.
I read that in this case I canīt use local temp tables but I can use global temp tables, then I changed in my Store, #Resultados by ##Resultados, bu the result was the same.
My Store is likely to his. Please help me.
INSERT ##Resultados (Planta, Etapa,GrupoEquipo,Equipo,Concepto,Fecha,Guardia,
Valor,idConcepto)
EXEC CalculosDiarios @Area,@Reporte,@FechaIni,@FechaFin,0
SELECT LEFT(RP.Grupo,3) + LEFT(RP.Equipo,12) + LEFT(RP.SubGrupo,2)
+ LEFT(D.Fecha,8) + D.Valor as Dato
FROM
ReportesPlantilla RP
LEFT JOIN
##Resultados D
ON
RP.Planta = D.Planta
AND RP.Etapa = D.Etapa
AND RP.GrupoEquipo = D.GrupoEquipo
AND RP.Equipo = D.Equipo
AND RP.Concepto = D.Concepto
AND D.Fecha BETWEEN @FechaIni AND @FechaFin
Can some one tell me how to display the fields of temporary table in the report? In the query a global temp table is created and in the end i am displaying the fields of that table. How do i do that?
View 1 Replies View RelatedHi,
I have some questions regarding Temporary tables.
I need to use a temporary table within a stored procedure (which is a transaction), should I use local temporary table or global temporary table?
If I use a temporary table in my transaction, do I destroy the temporary table at the end of the transaction, or just leave it, and let the sytem clean it up? If I destroy the temporary table at the of the transaction, what happens if another user session is accessing the temporary at the very moment?
Thanks.
Cathie
I am searching for information on paging large datasets, and have found
some that involve creating temporary tables in the database.
Before I head off and implement something, I have a number of issues
I'd like to bounce around here.
1. An example I found on MSDN involves creating a temporary table,
copying relevant columns to the row in the temp table. Why do
this, rather add the source tables primary keys into the temp table,
and do a join? Example; browsing Products Catalog which is
categorised into hierarchies. The MSDN version would have a temp
table created with a incrementing field which is used for the paging,
and then a number of fields are also copied from the products table to
the temp table - my question is why not simply copy the product primary
key into the temp table, and then join?
2. In real life, do people allow each user to create their own
temporary tables? If I have 1000 concurrent users, all wishing to
perform a page-based browse, I would be creating 1000 new temporary
tables. Do people consider default temp tables, that is, creating
a default temporary table for browsing each category in the products
table, for example?
3. Do you have any advice/tips for this type of problem?
Thanks!
JR.
In Access 97/2000, a local table can be created in the program database, while the data is stored in a linked database. This is useful for me to do a sort-of iterative drill-down on linked tables, e.g. filter table1 into table2, then further filter table2 into table3 and so on.....
If I do this I leave a trail that I can navigate in reverse (back up a step).
This is very important in my app.
So now my problem is....I'm trying to duplicate this in an ADP file using only ADO 2.5. I have no local tables, only server tables. If I create a temporary table, it only exists until the procedure ends.
Is there any way to create temporary tables that are specific to a user session and do not automatically delete themselves, or is there a better way to do this in SQL7?
on sql-server-performance.com i read :
Do not create temporary tables from within a stored procedure that is invoked by the INSERT INTO EXECUTE statement. If you do, locks on the syscolumns, sysobjects, and sysindexes tables in the TEMPDB database will be created, blocking others from using the TEMPDB database, which can significantly affect performance. [6.5, 7.0, 2000] Added 9-1-2000
I have a question does this negative effect also include simple SQL commands apart from stored procedures.
For example if from vb i execute a "Select into" temporary table. Will this have the same negative impact as with executing this from a stored procedure ?
Thank you very much
I have created a local temporary table (#Temp) and placed data in it.When I do:SELECT * #TempThe result is a set of rows with no columns.The SELECT statement is in the same procedure as the creation of #Temp; so Idon't believe this is a scoping issue. Does that make sense?Thank you in advance,Eric
View 3 Replies View Related
hi,
I'm going through some stored procedures that have been written previously.
In every sp, they are checking for existance of temporary table in sysobjects.
So, i made a simple script and checked if #TempTable has a entry in sysobjects.
But i did not find it.
On internet many ppl are talking about checking if temp table already exists.
So my question is...
1. What are circumstances under which temporary table doesn't get deleted
automatically.
2. Does temporary table have a entry in sysobjects?
Thanks
Sandeep
Hi -
I have been trying to work with a data flow task that uses temporary tables on the remote DB.
I have seen Jamie Thompson's SSIS Temporary table guide, available here: http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx, and successuflly got his download package working against my AdventureWorksDB.
I cannot get a temporary table implementation working in my SSIS package despite following JT instructions.
Current status:
Marked the OLEDB connection as retainsameconnection = true
Created a seperate execute sql task in control flow that creates the temporary table '#BUSessions'
A data flow task follows the execute sql task, the ole db source is marked a delayvalidation = true
At this point, I try to enter the query using the interface, validation kicks in and says this object is invalid. If I try to use the properties window to set sqlCommand and manually enter the query then an error icon appears on the Ole Db Source.
Can anyone help me with this?!?! I'd really appreciate it! The query provided to me (for the remote DB) uses temporary tables and I have little say in this...
p.s. - I have also ensured I manually create the global temporary table with same name before starting this process.
I am declaring a temp table in control flow via Execute SQL task. I then want to use that table as a source in a data task, keep it alive going back to control flow and use it as a destination in another data task following. I am having trouble just getting the first data flow to access that temp table.
In my Execute SQL task, I'm creating a simple temp table. After executing that task, I can head to the data task and change my OLE DB Source to point to that temp table, but I recieve an error about an invalid table name when I attempt to map columns or hit "OK."
Is there something else I need to do to use that temp table as a source or can I only reference it through SQL queries?
Thank you.
Hi,
This is my problem:
There is one table. I extracted some information on that one table and placed it in two temporary table.
Temporary Table 1
------------------------------------
ID Data 1 Data 2
1 xxxx yyyyy
2 zzzz aaaaa
3 bbbb ccccc
4 dddd eeeee
Temporary Table 2
------------------------------------
ID Data 1 Data 2
9 xxxx fffff
10 zzzz aaaaa
11 bbbb ccccc
12 dddd ggggg
Now, I want to get the only the unique values from both tables into a single table.
Temporary Table 3
------------------------------------
ID Data 1 Data 2
1 xxxx yyyyy
4 dddd eeeee
9 xxxx ffff
12 dddd ggggg
How do I do it?
Thanks!
Does SSIS (other than, maybe, via the Execute SQL task) support the idea of temporary tables? (I want to make a backup of production data (in temproary tables), truncate the production tables, and populate them with new data. If an error happens in the process, I'd copy the temporary backup tables back into the production tables. When the process has ended the temporary tables should "vanish")
TIA,
barkingdog