1
投票
3答案
1505 次观看

您是否应该使用临时表在存储过程之间传递数据?

我有许多搜索功能(存储过程),它们需要返回具有完全相同列的结果。 这是采取的方法: 每个存储过程都具有以下一般结构: CREATE TABLE #searchTmp (CustomerID uniqueIdentifier) INSERT INTO #searchTmp SELECT C.CustomerID FROM /**** do actual search here, based on stored proc arguments ****/ EXEC spSearchResults DROP T...

10
投票
4答案
9860 次观看

表变量在SQL Server存储过程中插入时性能较差

我们在存储过程中使用表变量遇到性能问题。 这是实际发生的事情: DECLARE @tblTemp TABLE(iId_company INT) INSERT INTO @tblTemp(iId_company) SELECT id FROM ..... SELECT返回138个结果,但是插入TABLE变量需要1分15分钟,但是当我使用具有相同SELECT的临时表时,请耗时0秒: CREATE TABLE #temp (iId_company INT) INSERT INTO #temp(iId_company) SELECT id FROM ... 什...

9
投票
4答案
8685 次观看

What is the difference between TEMPORARY TABLE and TABLE VARIABLE in SQL 2008?

What is the difference between: CREATE TABLE #temp ( [ID] INT) INSERT INTO #temp SELECT ... and DECLARE @temp TABLE ( [ID] INT) INSERT @temp SELECT ... in SQL Server 2008?

7
投票
3答案
13868 次观看

Global Temporary table delete operation

How to check if the global Temporary table exists in SQL server, if yes then delete that global temporary table? I am trying to execute this: IF OBJECT_ID('##Table', 'U') IS NOT NULL DROP TABLE ##Table ...but it is not working.

2
投票
4答案
6144 次观看

How to save a single result set to a temp table from a SQL stored procedure returning multiple sets?

I need to store the result set of a stored procedure in a temporary table (using SQL Server 2000). From what I've read, this (poorly constructed example) should work: create table #tempTable (TempId int primary key, Column1 varchar(100), Column2 varchar(100), DateCreated datetime) insert into...

0
投票
2答案
1977 次观看

My SQL Using temporary tables with PHP without mysql_pconnect

I want to use temporary tables in my PHP code. It is a form that will be mailed. I do use session variables and arrays but some data filled in must be stored in a table format and the user must be able to delete entries in case of typos etc. doing this with arrays could work (not sure) but I'm ki...

4
投票
2答案
3198 次观看

Entity Framework, full-text search and temporary tables

I have a LINQ-2-Entity query builder, nesting different kinds of Where clauses depending on a fairly complex search form. Works great so far. Now I need to use a SQL Server fulltext search index in some of my queries. Is there any chance to add the search term directly to the LINQ query, and hav...

15
投票
6答案
155710 次观看

How to return temporary table from stored procedure

CREATE PROCEDURE [test].[proc] @ConfiguredContentId int, @NumberOfGames int AS BEGIN SET NOCOUNT ON RETURN @WunNumbers TABLE (WinNumb int) INSERT INTO @WunNumbers (WinNumb) SELECT TOP (@NumberOfGames) WinningNumber FROM [Game].[Game] g JOIN [Game].[RouletteResult] AS rr ON g.[Id] = rr...

1
投票
3答案
1951 次观看

Using temp tables in SSIS

I've created an ADO.NET connection manager, and a DataReader source with the following SQL Command: select 'test' as testcol INTO #tmp select * from #tmp If I click the refresh button in the DataReader component, I get SqlException "Invalid object name #tmp". The SQL statment itself i...

1
投票
2答案
1913 次观看

SQL Oracle statement optimized with temporary table

I have optimized a complex Oracle statement using temporary table like this : original : SELECT data FROM table WHERE ..complex statement..; optimized (I can't use WITH keyword, because I deal with CREATE GLOBAL TEMPORARY TABLE temptab (x NUMBER, y DATE) ON COMMIT DELETE ROWS; INSERT INTO ...

4
投票
4答案
2146 次观看

Speeding up temp table joins in SQL Server

I have 2 temp tables #temp1 and #temp. Both have a key and date columns. Both have around 25k rows. And I'm left joining them on the basis of the key and date which are unique on all rows. It's taking around 4 minutes for this join to complete. Is there any way to speed it up or any alternative m...

7
投票
3答案
8846 次观看

sp_send_dbmail will not send query results

I've tried every avenue on every damn forum suggested, but to no avail! Need to send results of SQLPERF(logspace), that have been stored in a table, via sp_send_dbmail to recipient. Step 2 of job is where failure occurs. Please help! EXEC msdb.dbo.sp_send_dbmail @profile_name= 'MyDBA', @recipie...

2
投票
1答案
1871 次观看

MySQL connection pooling and creating temporary table

I already set up MySQL connection pool in Glassfish using JNDI. I only execute one query at a time but use the same sql instance. Everything seems to work fine except creating temporary tables and use them. In short, even though after I create a temporary table, insert query does not work since t...

26
投票
8答案
55080 次观看

SQL Server SELECT INTO and Blocking With Temp Tables

So, recently a DBA is trying to tell us that we cannot use the syntax of SELECT X, Y, Z INTO #MyTable FROM YourTable To create temporary tables in our environment, because that syntax causes a lock on TempDB for the duration of the stored procedure executing. Now, I've found a number of thing...

0
投票
2答案
1216 次观看

MySql: Problem when using a temporary table

I'm trying to use a temporary tables to store some values I need for a query. The reason of using a temporary table is that I don't want to store the data permanently so different users can modify it at the same time. That data is just stored for a second, so I think a temporary table is the best...

2
投票
3答案
2349 次观看

TempDB Log File Growth Using Global Temp Tables

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 cons...

2
投票
4答案
8464 次观看

How do I use a temp table across multiple c# calls

I have a C# application, using ADO.Net to connect to MSSQL I need to create the table (with a dynamic number of columns), then insert many records, then do a select back out of the table. Each step must be a separate C# call, although I can keep a connection/transaction open for the duration.

17
投票
3答案
44311 次观看

SQL Server shows "Invalid object name '#temp'" when working with a temporary table

I have created a procedure create procedure testProcedure_One as DECLARE @Query nvarchar(4000) begin SET @Query = 'SELECT * into #temptest FROM Table1' Exec sp_Executesql @query SELECT * FROM #temptest drop table #temptest end When I run the procedure testProcedure_One I am getting the er...

1
投票
3答案
8541 次观看

Sql server - how to insert single row into temporary table?

I have two temporary table, when i do cycle through one table and i get some values from it, in this cycle I need insert new row into another temporary table. Is this possible. Here is my sql code and error information: Alter PROCEDURE ProfitReportQ_Search_WithSub (@DateFrom datetime, @DateTo ...

0
投票
2答案
296 次观看

How to tell what temporary tables are currently in scope in SQL Server?

I often get the errors: Msg 208, Level 16, State 0, Line 1 Invalid object name '#foo'. Msg 3701, Level 11, State 5, Line 1 Cannot drop the table '#foo', because it does not exist in the system catalog. How do I know what temporary tables there are in scope? They obviosly don't show up in SSM...

0
投票
1答案
632 次观看

Stored procedure and .NET: SELECT INTO on temporary table: suppress result

I'm accessing a stored procedure from ADO.NET. The stored procedure should eventually returns a single result set. To compute this result, a temporary table is filled with a SELECT INTO statement. The problem is that the result of the SELECT INTO statement is also returned as a result set to ....

1
投票
4答案
28480 次观看

Local Temporary table in Oracle 10 (for the scope of Stored Procedure)

I am new to oracle. I need to process large amount of data in stored proc. I am considering using Temporary tables. I am using connection pooling and the application is multi-threaded. Is there a way to create temporary tables in a way that different table instances are created for every call t...

4
投票
3答案
125 次观看

Pulling items out of a DB with weighted chance

Let's say I had a table full of records that I wanted to pull random records from. However, I want certain rows in that table to appear more often than others (and which ones vary by user). What's the best way to go about this, using SQL? The only way I can think of is to create a temporary tabl...

3
投票
4答案
244 次观看

functional reason why stored procedures don't support INSERT/EXECUTE INTO?

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 somet...

0
投票
4答案
649 次观看

将另一个表的一部分插入到表中

我有两个表,第一个表部分概述,第二个表迭代: table1 (id, i, j, k, a, b, c, x, y, z) -- requests table2 (id, a, b, c, d) -- essential elements / bank subjects 我需要将table1中具有给定ID的记录插入table2中。 最好的方法是什么? 我有两个想法: 1: DECLARE @a type, @b type, @c type SELECT @a = a, @b = b, @c = c, FROM table2 WHERE id = @id INSERT ...

24
投票
9答案
90297 次观看

在临时表上创建主键-什么时候?

我有一个存储过程,正在处理大量数据。我将数据插入到临时表中。事件的整体流程类似 CREATE #TempTable ( Col1 NUMERIC(18,0) NOT NULL, --This will not be an identity column. ,Col2 INT NOT NULL, ,Col3 BIGINT, ,Col4 VARCHAR(25) NOT NULL, --Etc... -- --Create primary key here? ) INSERT INTO #TempTab...

4
投票
4答案
8643 次观看

在SQL Server中推荐一个好的临时表教程

我搜索了SQL Server 2005/2008中的新手,但找不到合适的临时表用法教程。我想了解临时表与普通表的优缺点,它的生存时间以及如何共享临时表(在同一会话中,跨会话)? 先谢谢了, 乔治

7
投票
4答案
4367 次观看

Linq中的临时表-任何人都对此有疑问吗?

在尝试解决: Linq。包含大量内容会导致TDS错误 我认为我偶然发现了一个解决方案,我想看看这是否是解决问题的一种粗略方法。 (简短摘要)我想对不是(完全或至少很容易)在SQL中生成的记录ID列表进行linq-join。这是一个很大的清单,并且经常超过TDS RPC调用的2100个项目限制。因此,我在SQL中所做的事情是将它们扔到临时表中,然后在需要它们时加入它们。 所以我在Linq中做过同样的事情。 在MyDB.dbml文件中添加: <Table Name="#temptab" Member="TempTabs"> <Type Name="...

63
投票
7答案
19680 次观看

您可以在SQL中定义“文字”表吗?

是否有任何SQL子查询语法可让您从字面上定义临时表? 例如,类似 SELECT MAX(count) AS max, COUNT(*) AS count FROM ( (1 AS id, 7 AS count), (2, 6), (3, 13), (4, 12), (5, 9) ) AS mytable INNER JOIN someothertable ON someothertable.id=mytable.id 这样可以省去两个或三个查询:创建临时表,将数据放入其中,然后在联接中使用它。 我正在使用MySQL...

0
投票
3答案
1738 次观看

如何将临时表中的数据分组

我创建了一个临时表,其中有一个对应的fileid是主键,我有5行。我想将计数设为5。我必须写什么查询来获取对应于每个fileid的计数?