I have the following inside a package and it is giving me an error: ORA-14551: cannot perform a DML operation inside a query Code is: DECLARE CURSOR F IS SELECT ROLE_ID FROM ROLE WHERE GROUP = 3 ORDER BY GROUP ASC; BEGIN FOR R IN F LOOP DELETE FROM my_gtt_1; COMMIT; INSERT INTO my_gtt_1 ...
I am creating a temp table with a query like this: CREATE TEMPORARY TABLE temp_table SELECT * FROM regular_table WHERE 1 But regular_table has FULLTEXT index on some of the fields. I try to do a FULLTEXT search on the new temporary table and I get an error telling me "Can't find FULLTEXT index matching the column list". So obviusly th...
I have to create a temp table (#temp) from a variable or stored procedure (i.e) My stored procedure contains ...... set @sql='select ...' set @sql=@sql+'..join..' set @sql=@sql+'....join..' when i execute it (i.e) exec (@sql) it returns some rows,i want to store that rows in a temp table.How to acheieve it? i tried something lik...
Hi, I need to findout a better and quick way to query MySQL table to get specific data for each day in a given date range, where the table has two date columns specifying start and end date. Table example: Promotions columns: ID startDate EndDate Discount CategoryID ======================================================...
How to insert the resultset given by the commands RESTORE FILELISTONLY RESTORE HEADERONLY RESTORE VERIFYONLY into an automatically generated temp table ? I would like to use a technique similar to (so the table is auto created, with all the columns matching the resultset's columns) SELECT * INTO #TempTable FROM (RESTORE FILELISTON...
Suppose I'm using the following Oracle code in a stored procedure: CREATE GLOBAL TEMPORARY TABLE temp_table ( field1 NUMBER, field2 NUMBER ) ON COMMIT DELETE ROWS This particular stored procedure may be called concurrently by different users at any single moment. As I understand it, the data visible to the user in the temporar...
I'm working with an Oracle 10g database, and I want to extract a group of records from one table, and then use that for pulling records out of a bunch of related tables. If this were T-SQL, I'd do it something like this: CREATE TABLE #PatientIDs ( pId int ) INSERT INTO #PatientIDs select distinct pId from appointments SELECT * fr...
Hi, I have the following SQL which gets a season for each day in a range of dates, then groups each season by start and end date with number of nights. What it does is not important but my question is which is better, the way I've done it below or use the first select statement as a subquery each time @dateSeasons is used in the second q...
I have been using stored procedure for more than 1.5 years. But I never thought how data is retrieved from UI or within another stored procedure. When I writes a simple stored procedure. eg. CREATE PROCEDURE sp_test AS BEGIN SELECT * FROM tblTest --Considering table has 3 columns. END How does C# gets this result into DataTable. Wh...
I'm curious as to how Informix (7.3) deals with temporary tables created with no log when a session is terminated without dropping those tables. I connect using iSQL: cat |isql db Then, for example, I create one or more temporary tables with no log: select first 10 * from table into temp t0 with no log; If I don't drop this table wi...
I have a stored procedure with a few steps. Two of the steps require the use of a DECLARE TABLE but I do not require these tables at the same time. The tables both have 2 BIGINT columns and may have up to 100 rows. Is it better practice to declare the two tables or to DELETE and re-use one? EDIT: If you're interested, this is a follow...
I write app in c#. I use temporary tables inside transaction. My server is sql 2005. Is there any performance threat, I read somewhere that using temporary tables inside transactions should be avoided ( http://www.sql-server-performance.com/tips/temp_table_tuning_p1.aspx post at the bottom of the screen added at 2-24-2003 ). ...
I write code in c#. I want to create temporary table before transaction begins and then use it within transaction, however when I try to do it I get error within transaction it estates that "Table does not exist". What is the proper way of doing it ? SqlConnection sqlConnection = new SqlConnection( "connstring" ); sqlConnection.Open()...
I have a MySQL DB with a user that has access only a few tables. This user has been granted CREATE TEMPORARY TABLES for this database and by watching the query logs, I can see them create a temporary table and then fail while trying to select from it. Doing GRANT SELECT ON TABLE 'db'.'tmp_tbl' TO 'user'@'localhost'; doesn't work as the t...
By trying this use master go select * into #TempTable from sys.all_views select * from #TempTable drop table #TempTable exec(' select * into #TempTable2 from sys.all_views ') /* This will give error: */ select * from #TempTable2 I realized that #TempTable2 is not accessible... so using the select into #TempTable syntax is used in...
I am using large number of global temporary tables for generating huge reports against an Oracle 10g database. Each report consists of 4 to 5 global temporary tables(GTT) per say. But as far as I understand the concept of GTT's, the data is created on the fly per each session for different set of parameters. For example, in my scenario,...
I'm working in SQL Server 2008, and I'm trying to select into a temp table based on a certain condition...for a report, I need up to 18% of the records to be of a certain product type. if ((@totalRecords * .18) > @productTypeCount) select * into #tmpLP_REIT from myTable where productType = @productType else select top 18 per...
Hi, According to what my needs it is better to use temp table or querystring? Click data in DbGrid and redirected to another Web site with ID data. ID I need, the need for ID data stored in the table. I thought even the temp table, which would temporarily stored ID and you would then find all the WHERE clause. What is the best solut...
Hi, I wonder if it is possible to assign temporary/ virtual IDs for a query result? For instance, I have this as my query, SELECT pg_id AS ID, pg_url AS URL, pg_title AS Title, pg_content_1 AS Content FROM root_pages ORDER BY pg_created DESC output: ID URL Title Content 53 a A xxx 40 b B xxx ...