The Age Old Argument Of Temp Table Vs Table Variable
Jan 26, 2007
Hi All,
Hope someone can help me...
Im trying to highlight the advantages of using table variables as
apposed to temp tables within single scope.
My manager seems to believe that table variables are not advantageous
because they reside in memory.
He also seems to believe that temp tables do not use memory...
Does anyone know how SQL server could read data from a temp table
without passing the data contained therein through memory???
Is this a valid advantage/disadvantage of table variables VS temp
tables?
I want to call a stored procedure that has a table variable as its parameter. I am using the MSFT SQL Server 2005 JDBC driver 1.1, however I don't know how to construct a table variable from the Java side to place within a CallableStatement. The CallableStatement interface allows me to set various types of parameters (e.g. Boolean, Byte, Blob, Array, etc.) but I don't see anything for table variable.
Hi. I want to call a stored procedure that has a table variable as its parameter. I am using the MSFT SQL Server 2005 JDBC driver 1.1, however I don't know how to construct a table variable from the Java side to place within a CallableStatement. The CallableStatement interface allows me to set various types of parameters (e.g. Boolean, Byte, Blob, Array, etc.) but I don't see anything for table variable.
Simple example:    declare @tTable(col1 int)    insert into @tTable(col1) values (1)    select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
In a previous post "Could #TempTable within SP cause lock on tempdb?" http://forums.microsoft.com/msdn/showpost.aspx?postid=2691763&siteid=1
It was obvious that we have to limit the use of #Temp table to a minimum. Let assume that some of the temp tables are really difficult to replace and we have to live with them.
Would it be easier on tempdb if the #TempTable is replaced by a table variable? Or do they all end up in tempdb?
Hi all, Im trying to use a temporary table along with a gridview. But i keep getting an issue that says that I need to declare the variable name. Right now, I have it set up that I have a permanent table in the database that is serving as my temporary table, however Im running into issues if multiple users access at once... So I would like to dynamically create a table and use that for my temporary table and then drop the table when the user is done with it... heres my code so far..Private Sub AddRecords(ByVal prodins As String, ByVal releaseins As String, ByVal opsysins As String, ByVal currelins As String, ByVal schemains As String, ByVal commentins As String, ByVal freeformins As String) Dim tablevar As String = lblRequestor.TextDim tablename As String = "##" + tablevar Dim myConnection As New SqlClient.SqlConnection Dim query As String = "CREATE TABLE " + tablename + "(UniqueID int Identity(1,1), Product varchar(50), OperatingSystem varchar(50), CurrentRelease varchar(50), Release varchar(50), SchemaSize varchar(50), Comments varchar(50), TempRelease varchar(50), FreeFormFlag varchar(50), sourcereleasedir varchar(125))" Dim query2 As String = "INSERT INTO " + tablename + "(Product, Release, OperatingSystem, CurrentRelease, SchemaSize, Comments, TempRelease, FreeFormFlag) VALUES ('" & prodins & "','" & releaseins & "', '" & opsysins & "', '" & currelins & "', '" & schemains & "', '" & commentins & "', '" & currelins & "', '" & freeformins & "')"Dim myCommand As SqlClient.SqlCommandmyConnection = New SqlClient.SqlConnection("Data Source=mydatasource") myConnection.Open()myCommand = New SqlClient.SqlCommand(query, myConnection) myCommand.ExecuteNonQuery()myCommand = New SqlClient.SqlCommand(query2, myConnection) myCommand.ExecuteNonQuery() myConnection.Close()
And here is my query for the gridview.... SelectCommand="SELECT * FROM @tablename where FreeFormFlag <> 'Y'" and <SelectParameters> <asp:ControlParameter ControlID="tablename" Name="tablename" PropertyName="Text" DefaultValue="##default" /> </SelectParameters> so @tablename is set to the value of a textbox i have hidden which the textbox gets the username of the user and adds ## to it..so if john doe comes on... the texbox reads ##jdoe... and thats what im trying to name the temp table. SO the syntax is all there.... But i keep getting an error saying i need to declare @tablename... which is puzzling to me.. I tried doing declare @tablename varchar (50) select.....etc.
but then it brings up an error saying... @tablename is already declared, each variable must be unique..., You must declare @tablename... It tells me that its already declared then tells me i must declare it??? it makes no sense..
Can someone send me an example of creating a variable to use instead of a temp table? I cannot find an example on books on line, but know it is possible in SQL2000.
It's SQL 2008 R2. I need to bring data from Oracle using .Net Providers/ODBC Data Provider to MS SQL table converting Oracle UTC dates to PST.  The source connection type cannot be changed as it's given. For the Destination I'm using the OLE DB.
As the truncate all and load could take time I'm trying to use a temp table or a variable to use it further with t-sql merge or not exists to bring/add the only new records to the destination table.
I'm trying different scenarios that is all failed.
Scenario A:
1. In DTF after OLE DB Source I'm using the Derived Colum to convert dates. It's working well.
2. Then use Recordset Destination with an object variable User::obj_TableACD. It's also working well.
3. Then I created a string variable with a simple query that I could modify later "select * from " + (DT_WSTR,10)@[User::obj_TableACD] trying to get data from the recordset object variable but it's not working.
Scenario B:
1. Created a store procedure to create a temp table.
2. Created a string variable to execute SP str_CreateTempTable: "EXEC dbo.TempTable". It's working well with the SQL Task with SQLSourceType as Variable.
3. Then how to populate the temp table from the Oracle source to bring data into the Destination?
Does abyone know how to compare data-type xml in a temp/variable/physical table in MSSQL 2000?
I tried this works in MSSQL 2005,
Code Snippet create Table #t1 ([c1] int identity(1,1) not null, [c2] text) create Table #t2 ([c1] int identity(1,1) not null, [c2] text) Insert into #t1 Values('This is a test') Insert into #t2 Values('This is a test') Select * from #t1 Select * from #t2 Select * from #t1 where [c2] LIKE (Select [c2] from #t2) drop table #t1 drop table #t2
but not MSSQL 2000.
Server: Msg 279, Level 16, State 3, Line 12 The text, ntext, and image data types are invalid in this subquery or aggregate expression.
Is this true (from BOL)?
Code SnippetIn comparing these column values, if any of the columns to be compared are of type text, ntext, or image, FOR XML assumes that values are different (although they may be the same because Microsoft® SQL Server„¢ 2000 does not support comparing large objects); and elements are added to the result for each row selected.
hai <br> <p>i have a procedure where in which i want to access a specific table whose name will be passed into procedure as argument. Here is the peice of code i have made.</p> CREATE PROCEDURE outputtable @tablename nchar(10) AS<br> begin<br> .........................<br> insert into @tablename (classno) values (@cno)<br> .................................<br> END<br>
<p> I get the following error message :</p> Msg 1087, Level 15, State 2, Procedure................<br> Must declare the table variable "@tablename".<br> <p> Is there anyway i can get around this one. I will be grateful if anyone can help me</p>
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it. --no compilation error. CREATE PROC testTemp AS BEGIN INSERT INTO #tmp(dt) SELECT GETDATE() END
only on calling the proc does this give an execution error
If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.
Example of the script is below: in the source project I added columns [MyColumn_LINE_1]  and [MyColumn_LINE_5].
Is there any way I can make it generating an alter statement instead?
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; CREATE TABLE [dbo].[tmp_ms_xx_MyTable] ( [MyColumn_TYPE_CODE] CHAR (3) NOT NULL,
[Code] ....
The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.
The SP UserPersist_GetByCriteria does a "SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture", so why am I receiving this error when both tables have the same structure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.
I think this is a very simple question, however, I don't know the answer. What is the difference between a regular Temp table and a Global Temp table? I need to create a temp table within an sp that all users will use. I want the table recreated each time someone accesses the sp, though, because some of the same info may need to be inserted and I don't want any PK errors.
I want to insert the data from temp table to other table. Only condition is, it needs to sorted based on tool number and tool date. For example if we have ten records for tool number 1000, it should be order by tool number and then based on tool_dt. Both tables doesn't have any primary keys. Please find below my code. I removed all the unnecessary columns for simple understanding. INSERT INTO tool_summary  (tool_nbr, tool_dt) select tool_nbr, tool_dt from #tool order by tool_nbr, tool_dt...But this query is not working as expected. Data is getting shuffled.
WE have a job that loads data from an Oralce DB into our SQL Server 2000 DB twice a day. The schedule has just changed so that now there is a possibility of having my west coast users impacted when it runs at 5 PM PST and my east coast users impacted when it runs at 7 AM EST. As a workaround, I have developed a DTS package that loads the data into temp tables instead of the real tables. IE. Oracle -> XTable_temp instead of Oracle -> XTable. The load sometimes takes about an hour to an hour and a half to load, so this solution works great, but I want to then lock the table, delete it and rename the temp table to table X. The pseudo code would be:
Begin Transaction
Lock Table XTable
Drop XTable
Alter Table XTable_temp rename to XTable
Release Lock XTable
End Transaction
Create XTable_temp
I see two issues with this solution. 1) I think if I can lock XTable that the lock would be released when the table is dropped and the XTable_temp was being renamed. 2) I can't find a command to rename a table.
I want to pass the 'inserted' table from a trigger into an SP, I think I need to do this by dumping inserted table into a temporary table and passing the temp table. However, I need to do this for many tables, and don't want to list all the column names for each table/trigger (maintenance nightmare).
Can I dump the 'inserted' table to a temp table WITHOUT specifying the column names?
I need to decide what is better to use: global temp table ( I can't use local one) or permanent table in SQL 2000 stored procedures. I extract data from linked server table and update several tables on our server. Those procedures scheduled to run every 3 hours.
Another question: for some reasons when I used global temp table, I wasn't able to schedule multi steps with every step executing one of the stored procedures.I think global temp tables should be visible to other stored procedures, right?
