Why can't you do this and is there are work around? You get this error. Msg 2714, Level 16, State 1, Line 13 There is already an object named '#temptable' in the database. declare @x int set @x = 1 if (@x = 0) begin select 1 as Value into #temptable end else begin select 2 as Value into #temptable end select * from #temptabl...
My database contains three tables called Object_Table, Data_Table and Link_Table. The link table just contains two columns, the identity of an object record and an identity of a data record. I want to copy the data from DATA_TABLE where it is linked to one given object identity and insert corresponding records into Data_Table and Link_T...
I have a TSQL Stored Procedure tsql__sp__A which does two things: (a) Creates a temp table #tempTable that has SELECT data from a complex SELECT query. (b) Calls a CLR managed Stored Procedure clr__sp__B for each row that does computation on row parameters. Question: Is it possible to access #tempTable from CLR procedure clr__sp_...
I'm trying to write a stored procedure that will read an Excel file into a temp table, then massage some of the data in that table, then insert selected rows from that table into a permanent table. So, it starts like this: SET @SQL = "select * into #mytemptable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database="+@file+";HD...
I'm an MSSQL guy, but I'm working on a DB2 query that needs to create a temp table, insert into it, and do stuff with it. As a much-shortened test, I'm using the following query, which is providing the same result.. declare global temporary table tt_testingSyntax (id int); insert into session.tt_testingSyntax (id) values (1); insert i...
In SQL Server, there's no way to create a temp table on the fly from the results of a stored procedure, ala: CREATE TABLE #temptable AS EXEC spMyStoredProc or EXEC spMyStoredProc INTO #temptable or something like that. Instead, you have to know the SP layout beforehand, and have to do something like this: CREATE TABLE #temptable (...
Hi Everyone, This is really a two prong question. One, I'm experiencing a phenomenon where SQL server consumes a lot of tempDB log file space when using a global temp table while using a local temp table will consume data file space? Is this normal? I can't find anywhere on the web where it talks about consuming log file space in such...
I have the following procedure: CREATE PROCEDURE foo () SELECT * FROM fooBar INTO TEMP tempTable; -- do something with tempTable here DROP TABLE tempTable; END PROCEDURE; What happens if there is an exception before the DROP TABLE is called? Will tempTable still be around after foo exits? If so, foo could fail the next ...
I am creating temp tables (#temp_table) in my stored procedure. It is a huge table with large data. Then I am creating a index in the storeed procedure as it is required for faster query to the temp table. But when I execute the stored procedure, the index is not used. The index is not being created when the stored procedure is executed....
I have had an issue with our replication process and would like to salvage some data. I have a process in place where I will connect to each subscriber before flagging them for reinitialization and I will run the below to pull any data they may have entered in during the "dark time". I am pretty sure this will work in a vanilla palace...
I have a question about how MS SQL evaluates functions inside CTEs. A couple of searches didn't turn up any results related to this issue, but I apologize if this is common knowledge and I'm just behind the curve. It wouldn't be the first time :-) This query is a simplified (and obviously less dynamic) version of what I'm actually doing...
I see some code where the author has truncated a temp table immediately before dropping the temp table. Is there a reason for doing this? TRUNCATE TABLE #Temp DROP TABLE #Temp ...
I've been looking into SQL recently and exploring a bit. in regards to Temp Tables I have discovered 3 different temp table types: 1) CREATE TABLE #TempTable 2) DECLARE TABLE @TempTable 3) SELECT * FROM (SELECT * FROM Customers) AS TempTable Now I understand the scope behind the #TempTable and the @TempTable types, but what about the...
Hello, excuse my english First of all, I am new to optimizing mysql. The fact is that I have in my web application (around 400 queries per second), a query that uses a GROUP BY that i can´t avoid and that is the cause of creating temporary tables. My configuration was: max_heap_table_size = 16M tmp_table_size = 32M The result: temp ...
I am building a database that contains public, private(limited to internal) and confidential data (limited to very few). It has very specific requirements that the security of the the data is managed on the database side, but I am working in an environment where I do not have direct control of the permissions, and requests to change the...
Here is the SQL: CREATE TABLE dbo.TmpFeesToRules1(Name varchar, LookupId int) INSERT INTO dbo.TmpFeesToRules1(Name, LookupId) SELECT DISTINCT Name, 0 FROM Lending.Fee UNION SELECT DISTINCT Name, 0 FROM Lending.ApplicationFee INSERT INTO dbo.tblLookup (LookupType, LookupCode, LookupDesc, EditFlag, DeletedFlag, DefaultFlag) SELECT 'FEE_...