they all seem same to me. Is there any big difference among them.
Also, if I do have the tables avaiable, so now I don't think I have to create any of the temerory tables,
since I can use the
WITh mystatement ( )
Select * from mystatmment
I think if I have to use any of the temperory tables , only time when I have to just create some tables to test some values on in it rather then using the real table in the database.
2) And not only that I use the CTE for that but for any sub query whereever apply, i would like to forget about the IN, Exit or = in the correlated queries but would use whereever I can use the "Joins"
Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.
However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.
Here is the code that works:SET NOCOUNT ON
CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000)) INSERT ##MyTempTbl values ('Put your long message here.') INSERT ##MyTempTbl values ('Put your second long message here.') INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!') DECLARE @cmd varchar(256) DECLARE @LargestEventSize int DECLARE @Width int, @Msg varchar(128) SELECT @LargestEventSize = Max(Len(MyWords)) FROM ##MyTempTbl
SET @cmd = 'SELECT Cast(MyWords AS varchar(' + CONVERT(varchar(5), @LargestEventSize) + ')) FROM ##MyTempTbl order by SeqNo' SET @Width = @LargestEventSize + 1 SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------' EXECUTE Master.dbo.xp_sendmail 'YoMama@WhoKnows.com', @query = @cmd, @no_header= 'TRUE', @width = @Width, @dbuse = 'MyDB', @subject='none of your darn business', @message= @Msg DROP TABLE ##MyTempTbl
The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.
Any insight anyone? Or is BOL just full of...well..."stuff"?
I have an application that I am working on that uses some small temptables. I am considering moving them to Table Variables - Would thisbe a performance enhancement?Some background information: The system I am working on has numeroustables but for this exercise there are only three that really matter.Claim, Transaction and Parties.A Claim can have 0 or more transactions.A Claim can have 1 or more parties.A Transaction can have 1 or more parties.A party can have 1 or more claim.A party can have 1 or more transactions. Parties are really many tomany back to Claim and transaction tables.I have three stored procsinsertClaiminsertTransactioninsertPartiesFrom an xml point of view the data looks like this<claim><parties><info />insertClaim takes 3 sets of paramters - All the claim levelinformation (as individual parameters), All the parties on a claim (asone xml parameter), All the transactions on a claim(As one xmlparameter with Parties as part of the xml)insertClaim calls insertParties and passes in the parties xml -insertParties returns a recordset of the newly inserted records.insertClaim then uses that table to join the claim to the parties. Itthen calls insertTransaction and passes the transaction xml into thatsproc.insertTransaciton then inserts the transactions in the xml, and alsocalls insertParties, passing in the XML snippet
I am trying to pass a variable set by a Row count transformation to a derived column transformation. There are actually three separate Row Count transformations storing to different variables. In the derived column trans. the expression i am using is @[User::ClientCount] and the variable type is Int32 and I am passing it to an I4 database column. After the derived column trans. all rows just come out with 0s in the row count columns. Does anyone know why this would be? Please let me know if you need more information.
Trying to setup a derived column to use an expression stored in a package variable. But it seems that variables are always evaluated as text...I need it to evaluate as a Columname sometimes.
Example: On an ETL of Products I want a new derived column that uses two other columns.
I can hardcode an expression of ProductID + " - " + ProductName and that results in dynamic output. But now I want to use that as an expression stored in a variable so I can change it when needed.
So I make that expression = @[User::Variable] and stuff into @variable ( a string param) : ProductID + " - " + ProductName My output is the literal "ProductID + " - " + ProductName", and not the actual ID's and Names
I've tried with/without brackets, quotes and braces but no change.
Any way I can get the pieces in that variable expression to evaluate as column names?
In the derived column transformation editor, I have a Derived column name called FileGroupID. I would like to pass in a value for this column from a variable that I have set earlier in the scope. Can someone let me know, how to write the expression that does that and where do I specifiy that expression. I am thinking its the expression field in the derived column transformation editor. My main question is how to actually write the expression, what is the syntax to pull the variable value? Thanks.
I have 3 Checkbox list panels that query the DB for the items. Panel nº 2 and 3 need to know selection on panel nº 1. Panels have multiple item selection. Multiple users may use this at the same time and I wanted to have a full separation between the application and the DB. The ASP.net application always uses Stored Procedures to access the DB. Whats the best course of action? Using a permanent 'temp' table on the SQL server? Accomplish everything on the client side?
[Web application being built on ASP.net 3.5 (IIS7) connected to SQL Server 2005)
I am trying to extract value from temp.variable and create new column in temp.table using that value for .eg
@name = 'abc'
What command should I use in my S.P to extract abc from @name, so I can create new column called abc in my temp. table.If there are any other ways of doing thinsg let me know.
Hi , Can anyone guide me to resolve my problem . I need to write a procedure which first looks for the Worker names from WORKER table who satisfies certain criterias , and then Find from another table how many jobs each one has done on each day of a month . I have written a function which will return all days of a particular month, which can be used for the above procedure .If the wrokers are John , Alex and Martin ,( which may vary according to the branch parameter) The report should look like
Day John Alex Martin 1/5/2004 4 8 NULL 2/5/2004 5 9 12 ------------------------ etc
Thanks in advance Regards Praveen ( praveenvc@rediffmail.com)
Hi. We have to create an export from our system to be imported into another system. To get the data out we need to create some SQl but we're struggling a bit.
Hi, I wanna know is there any advantage of perf gain when using Derived Tables over Temp Tables, advice me which one is better to use. Can I create Indexes and Insert/Update records into Derived Tables.
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.
This is kind of what I'm trying to do in my MS SQL 2000 query. ShouldI be able to reference s1.col1 inside the 2nd derived table?I'm getting 'Invalid column name col1' and it's coming from the 2ndderived table (I've commented out other refs to just it to check).Maybe I need to use a temp table instead.SELECT s1.col1,(SELECT * FROM(SELECT COUNT(zzz) AS SomeTotalFROM tab1WHERE s1.col1 = zzz)) AS RowCount) /* error here */FROM(SELECT col1 FROM table) AS s1Thank you!
Hi, In the SQL92 Specifications i read the foloowingf statement... "All base tables are updatable. Derived tables are either updatable or read-only. The operations of insert, update, and delete are permitted for updatable tables, subject to constraining Access Rules. The operations of insert, update, and delete are not allowed for read-only tables." But i am concentrating on the below line from the above written lines, "Derived tables are either updatable or read-only." I want to ask that is derived tables are updatable or not??? if yes then how,???i tried the following querry but its not working...
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?
Hi!I want to return a derived table along with 4 simple tables in a storedprocedure as follows:Input parameter: @FtNum (==Order Number, selects one Order and allassociated data)Table 1: OrdersTable 2: ItemsTable 3: InstancesTable 4: StockDetailsDerived Table: for each Item that requires stock items, 1st columnshould receive the ItemNo (from Items), subsequent columns should receive thedetails from StockDetails via the common key field 'StockCode'.I have so far used a 'Fetch' cursor to find all occurrences of a StockCodewithin the Items table, but have been unable to figure out how to first addthe ItemNo into the temporary table.Code is as follows:... build #tmp_StockDECLARE stock_cursor CURSOR FORSELECT StockCode, ItemNoFROM ItemsWHERE FtNum = @FtNumORDER BY ItemNoOPEN stock_cursorFETCH NEXT FROM stock_cursorINTO @StockCode, @ItemNoWHILE @@FETCH_STATUS = 0BEGININSERT INTO #tmp_Stock-- wish to insert ItemNo = @ItemNo here --SELECT *FROM ControlledStockWHERE StockCode = @StockCodeFETCH NEXT FROM stock_cursorINTO @Stockcode, @ItemNoENDOf course there may be a much simpler way to do this!Your help would be greatly appreciated either way.--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200510/1
Hello.So the scenario is a little complicated.I am joining two tables.Table 1 is derived; it has one row; it has a column based from newid()Table 2 joins to table 1 and reuses the newid() value from table 1 in table 2's rowsBecause there is only one row in Table 1, the value of newid() REPEATS in Table 2The bug is that the NewId() value from Table1 is REGENERATED with every Table 2 record.I created a blog about this because it takes a code sample to demonstrate:http://jerrytech.blogspot.com/2008/04/sql-2005-tsql-bug-with-newid-in-derived.html
create multiple INNER JOIN on derived tables as I have written below or use a #temp table for all derived tables and use them into JOIN. This below query is also very hard to understand what is going on .
Relative SQL newbie here......this is probably easy, but....Lets say I have a table (MainTable) that stores a list of input table names,a primary key (PKey), and a field called "Configured" for each one. Each ofthese input tables also contain a field called "Configured", which is set totrue or false in another process based on an OrderNumber. (So an order'sinputs are stored in several input tables, and the MainTable is a summarytable that shows which input tables have been configured for any givenOrderNumber).What I need to do is open each input table, and look for a record containinga specific OrderNumber and where Configured=true. If a record is found, Ineed to update the Configured field for that table in the MainTable, andthen move on to the next sub-table.The way I'm doing it now is with simple SQL and loops. Here is the basiccode (ASP):*****************************************OrderNumber = "562613" ' the current order that is being processed' reset all configured flagssql = "UPDATE MainTable SET Configured = 0"conn.execute sql, , &H00000080' get list of all tablenamessql = "SELECT InputTableName, PKey FROM MainTable WHERE InputTableName <>'---'"set rsTableNames = conn.execute(sql)while not rsTableNames.eof' test each input table for configured flagsql = "SELECT Configured FROM " & rsTableNames("InputTableName")& _" WHERE Configured = 1 AND OrderNumber = '" & OrderNumber &"'"set rs = conn.execute(sql)If Not rs.EOF Then' update the main tablesql = "UPDATE MainTable SET Configured = 1 WHERE PKey='" &rsTableNames("PrimaryKey") & "'"conn.execute sql, , &H00000080end ifset rs = nothingrsTableNames.movenextwend*****************************************There has to be a faster way.. I think.... maybe something that could bewritten as a stored procedure? I use a similar technique in a couple ofother places and it's a bit of a performance hit, especially as the numberof input tables grows.TIA!Calan
I am using derived tables to Page data on the SQL Server side.I used this link as my mentor for doing paging on the SQLServerhttp://msdn2.microsoft.com/en-us/library/ms979197.aspxI wanted to use USER PAGING, thus I used the following code:CREATE PROCEDURE UserPaging(@currentPage int = 1, @pageSize int =1000)ASDECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)SET @rowsToRetrieve = (@pageSize * @currentPage)SET NOCOUNT ONSET @SQLSTRING = N'selectCustomerID,CompanyName,ContactName,ContactTitle from( SELECT TOP '+ CAST(@pageSize as varchar(10)) +'CustomerId,CompanyName,ContactName,ContactTitle from( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)As T4 ORDER BY contactname ASC'EXEC(@SQLSTRING)RETURNGOWhen I use this. Assume that the Total records returned by the SQLquery is 1198.Thus when I am on Page1 the above Stored Proc (SP) willreturn the first 1000 records.This works absolutely fine.Now I am on Page2, now I need to retrieve only the remaining 198records.But if I use the above SP, it will return the last 1000records.So to tweak this I used the following logic to set the@pagesize variable:Declare @PageCount intselect @PageCount = @TotalRows/@PageSizeif @currentPage @PageCount SET @PageSize = @TotalRows%@PageSizeSince I am on Page2 the above logic will set the PageSize to 198 andnot 1000.But when I use this logic, it takes forever for the SP toreturn the 198 records in a resultset.However if the TotalRows were = 1800, and thus the PageSize=800 orgreater, this SP returns the resultset quickly enough.Thus to get over this problem I had to use the other logic i.e. usingApplication Paging (i.e. first storing the entire result set into aTemp table, then retrieving only the required records for the PAGE)Can anyone suggest what is wrong with my user paging logic?????TIA...
I did use query plans to find out more. ( Please see the thread BELOW)I have a question on this, if someone can help me with that it will begreat.In my SQL query that selects data from table, I have a where clausewhich states :where PermitID like @WorkTypeorder by WorkStart DESC@WorkType is a input parameter to the Stored proc and its value is'01%'When I use the above where clause, all the Sorts in the ESTIMATED QueryExecution plan show me a COST of 28%.However if I change the query manually to say:where PermitID like '01%'order by WorkStart DESCThe COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%and at the beginning of the PLAN, there is a Bookmark Lookup whichincludes the above where clause.Whereas with the FIRST example , the BookMark Lookup in the beginningdoesn't show that where condition.Can anyone help me better understand this anomaly?TIA=====================================I am using derived tables to Page data on the SQL Server side.I used this link as my mentor for doing paging on the SQLServerhttp://msdn2.microsoft.com/en-us/library/ms979197.aspxI wanted to use USER PAGING, thus I used the following code:CREATE PROCEDURE UserPaging(@currentPage int = 1, @pageSize int =1000)ASDECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)SET @rowsToRetrieve = (@pageSize * @currentPage)SET NOCOUNT ONSET @SQLSTRING = N'selectCustomerID,CompanyName,ContactName,ContactTitle from( SELECT TOP '+ CAST(@pageSize as varchar(10)) +'CustomerId,CompanyName,ContactName,ContactTitle from( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)As T4 ORDER BY contactname ASC'EXEC(@SQLSTRING)RETURNGOWhen I use this. Assume that the Total records returned by the SQLquery is 1198.Thus when I am on Page1 the above Stored Proc (SP) willreturn the first 1000 records.This works absolutely fine.Now I am on Page2, now I need to retrieve only the remaining 198records.But if I use the above SP, it will return the last 1000records.So to tweak this I used the following logic to set the@pagesize variable:Declare @PageCount intselect @PageCount = @TotalRows/@PageSizeif @currentPage @PageCount SET @PageSize = @TotalRows%@PageSizeSince I am on Page2 the above logic will set the PageSize to 198 andnot 1000.But when I use this logic, it takes forever for the SP toreturn the 198 records in a resultset.However if the TotalRows were = 1800, and thus the PageSize=800 orgreater, this SP returns the resultset quickly enough.Thus to get over this problem I had to use the other logic i.e. usingApplication Paging (i.e. first storing the entire result set into aTemp table, then retrieving only the required records for the PAGE)Can anyone suggest what is wrong with my user paging logic?????TIA...
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.
I am attempting to execute a stored procedure as the sql query for a data transformation from sql into an excel file. The stored procedure I am calling uses temp tables (#tempT1, #tempT2, etc.) to gather results from various calculations. When I try to execute this sp, I get 'Error Source: Microsoft OLE DB Provider for SQL Server Error Description: Invalid Object name "#tempT1"'
Is there a way to make a DTS package call a stored procedure that uses temp tables?
I want to check to see if a temporary table exists before I try creating one but I can't seem to find which sys table or schema collection I check. Any ideas?
-- Add Structural data usp_AddStructural @iEstimateID, 1, 'Structural' usp_AddForming @iEstimateID, 2, 'Forming' ... ... ... GO
Now, a couple of problems, after the table is created and populated, I cannot find it in my list of tables, even after "refreshing".
I checked to ensure that it exists using the query analyzer and it does so I know the table is being created.
Also, I cannot see the table using crystal reports, connecting etc...... Can I not access a temporary table from 3rd party applications? I have crystal reports 7.0 professional.
I am in the process of modifying some stored procedures that currently do not use temp tables. For this modification I am required to make the stored procedures use temp tables. There are several UDF's within this stored procedure that will need to use the temp tables, and this is where in lies the problem. Does anyone know of a work around that would allow UDF's to use temp tables, or does anyone know of alternate methods instead of temp tables that wouldn't involve too much change?
I have a called stored procedure which creates a bunch of temporary tables, inserts data into them, indexes the tables and then returns to the main calling SP. In the main stored procedure I then do SELECTs from the temporary tables. My problem is I keep getting invalid object errors on the temporary tables: Invalid object name '#temp_table1'
The stored procedure is in a test environment. In the SELECT I tried a prefix of database owner (my logon) as well as "dbo." but still get the error. Any suggestions as to what I am doing wrong would be much appreciated.
