TSQL Define Temp Table (or table variable) Without Defining Schema?
Is there a way to define a temp table without defining it's schema up front? ...
table-variable
Optimize multiple joins with table functions
I would like to join several times with the same table function for different input variables in the same query. But this turns in my case out to be much slower than using table variables and selecting from the table functions separately. How can I avoid table variables and still have a fast query? For example, we have a SQL query like...
Using a table variable inside of a exists statement
I am trying to update a column inside of a table variable based on a condition, the condition being that the ID of the table variable does not exist in a different table: DECLARE @BugRep TABLE(BugCode VARCHAR(50),DevFirstName VARCHAR(50), DevLastName VARCHAR(50), BugDate VARCHAR(20), IsValid VARCHAR(1)) UPDATE @BugRep SET IsValid =...
Can I loop through a table variable in T-SQL?
Is there anyway to loop through a table variable in T-SQL? DECLARE @table1 TABLE ( col1 int ) INSERT into @table1 SELECT col1 FROM table2 I use cursors as well, but cursors seem less flexible than table variables. DECLARE cursor1 CURSOR FOR SELECT col1 FROM table2 OPEN cursor1 FETCH NEXT FROM cursor1 I would like to be ...
Table variable poor performance on insert in SQL Server Stored Procedure
We are experiencing performance problems using a table variable in a Stored Procedure. Here is what actually happens : DECLARE @tblTemp TABLE(iId_company INT) INSERT INTO @tblTemp(iId_company) SELECT id FROM ..... The SELECT returns 138 results, but inserting in the TABLE variable takes 1min15 but when I use a temp table with the ...
Can queries that read table variables generate parallel exection plans in SQL Server 2008?
First, from BOL: Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. For more information, see CREATE TABLE (Transact-SQL). Que...
T-SQL copying a table variable
I'm trying to make a copy of a table variable: DECLARE @lt_Sections TABLE ( teamId SMALLINT NOT NULL ) DECLARE @lt_tempSections TABLE ( teamId SMALLINT NOT NULL ) -- populate some values in @lt_Sections -- take a copy of @lt_Sections SET @lt_tempSections = @lt_Sections This is giving me an error: Msg 137, Level 15, State 2, Li...
ODBC Execute/Fetch of SQL 2005 stored procedure result set cannot use a table @variable
I'm using ODBC and C++ against SQL Server 2005 (native client). I have the following simple test stored procedure that returns two rows of constant values: CREATE PROCEDURE usp_testme AS BEGIN declare @details table( one int, two int, three int, four int ) insert @details SELECT 1 one, 2 two, 3 three, 4 four UNION SELECT 5, ...
SQL error: String or binary data would be truncated
I got a table variable @RQ, I want it updated using a table-valued function. Now, I think I do the update wrong, because my function works... The function: ALTER FUNCTION [dbo].[usf_GetRecursiveFoobar] ( @para int, @para datetime, @para varchar(30) ) RETURNS @ReQ TABLE ( Onekey int, Studnr nvarchar(10), Stu...
SQL Server Multi-statement UDF - way to store data temporarily required
Hello, I have a relatively complex query, with several self joins, which works on a rather large table. For that query to perform faster, I thus need to only work with a subset of the data. Said subset of data can range between 12 000 and 120 000 rows depending on the parameters passed. More details can be found here: http://stackoverf...
Using table variables in stored procedures versus merely selecting from tables or a view?
I'm looking at sprocs right now that seem to follow the behavior demonstrated below DECLARE @tablevar TABLE ( FIELD1 int, FIELD2 int, FIELD3 varchar(50), -- etc ) INSERT INTO @tablevar ( FIELD1, FIELD2, FIELD3, -- etc ) SELECT FIELD1, FIELD2, FIELD3, -- etc FROM TableA Inner Join TableB on TableA.F...
Why do table variables work when I execute this query in SQL Server Studio but not via odbc_exec?
The query in the PHP code below works fine when I run it via Microsoft SQL Server Management Studio, but gives Warning: odbc_fetch_into() [function.odbc-fetch-into]: No tuples available at this result index at the odbc_fetch_into call (and does not have any rows). $sql = "DECLARE @ValueList TABLE (CheckValue int) INSERT INTO ...
Table variables inside while loop not initializing everytime : SQL Server
Hi, I am wondering why the table variables inside while loop does not behave like other variables. Table variables created only once and will be used across through out whole looping. but other variables getting initialized every time when loop increases. Check out the below code for more info declare @tt int set @tt =10 while @tt...
Exec an SQL-Server 2008 stored-procedure from Access, passing a TABLE variable
I need to pass a table from Access to SQL-server and execute a stored-procedure. I'm using pass-through queries in Access to do this. My pass-through query: DECLARE @MyVar TABLE { .....<variables> } INSERT INTO @MyVar SELECT * FROM [MyTable] EXEC sproc_test @Myvar My stored-procedure: ALTER PROCEDURE [dbo].[sproc_test] @M...