Stack Overflow
Stack Overflow

Questions tagged [table-variable]

A SQL Server local variable that can store rows.

From MSDN:

Is a special data type that can be used to store a result set for processing at a later time. table is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function.

A table variable differs from a "temporary table" in that it has

  • no statistics
  • limited index options
  • execution plans often assume single row
  • does not participate in user transactions
  • for the batch/proc scope only
170 questions
1
vote
3 answers

Returning or outputting a @tableVariable in SQL

Is it possible to return or output a @tableVariable in SQL Server? For example for the following stored procedure, how do I return the @TSV table variable? ALTER PROCEDURE MyStoredProdecure @Parameter1 INT, @Parameter2 INT AS BEGIN …
hermann
  • 6,237
  • 11
  • 46
  • 66
1
vote
3 answers

SELECT statement that executes instantly, takes forever to insert into table variable

I have the following SQL code below. It is taking forever to complete but if I run just the SELECT DISTINCT ID, ParentID FROM Y WHERE ID IN (3,4), it completes instantly. DECLARE @table TABLE ( ID int, ParentID int ) INSERT INTO @table SELECT…
Matt
  • 25,943
  • 66
  • 198
  • 303
1
vote
1 answer

How to add subset of fields from stored procedure to table variable?

Declare @tempTableVariable Table( email varchar(50) ) Insert INTO @tempTableVariable EXEC GetData select email from @tempTableVariable I get the following error: "Column name or number of supplied values does not match table definition." Is…
user1081822
  • 21
  • 2
1
vote
2 answers

system stored procedures and table parameters

I'm accustomed to doing this: use MyDb go create type table_list as table (name sysname) go create proc rebuild_indexes @ls table_list readonly as [...] but now I want to create the proc in master and use it in other databases: use…
ekkis
  • 9,804
  • 13
  • 55
  • 105
1
vote
1 answer

subquery X temp table X dynamic sql X table valued function

I have a view that summarizes a lot of info for my items. Let's call it v_item_details. This view is defined via recursive cte's, and can become very slow if the right filtering is not applied. Basically, if I provide a list of item id's, things…
user1139216
  • 109
  • 1
  • 8
1
vote
1 answer

Variable amount of sets as SQL database tables

More of a question concerning the database model for a specific problem. The problem is as follows: I have a number of objects that make up the rows in a fixed table, they are all distinct (of course). One would like to create sets that contain a…
avanderm
  • 131
  • 2
  • 7
0
votes
0 answers

How to use a temporary table or table variable within a function when Dynamic SQL required?

Goal: Create a table (Changes) to record any changes that take place within a db with the following information: Schema Table ColumnChanged DeletedValue InsertedValue When creating the triggers for this I want to make and utilize a function…
HelpImdumb
  • 1
  • 2
0
votes
2 answers

How to improve the performance of the SQL query which uses table variable which has more than 50k entries?

I have to update two temp tables @tbNewEntry and @tbUpdateEntry from another temp table @OnlineDataBase (I got the Data from online into this table) based on some conditions. Conditions to update: Update @tbNewEntry if CityCode is not present in…
Lax
  • 21
  • 1
  • 9
0
votes
0 answers

Subquery as parameter into SQL Server UDF

My question is similar to this. I made a scalar function like follows: CREATE FUNCTION [dbo].[MyFunction](@table [TableModel] READONLY) RETURNS DECIMAL(18, 6) AS BEGIN DECLARE @sql NVARCHAR(MAX), @params NVARHCAR(MAX), @value DECIMAL(16, 8); …
Bruno Ramirez
  • 46
  • 1
  • 8
0
votes
0 answers

Temp table causing full scan on table unlike Table Variable in a left outer join queries

If I switch out the @GameIds table variable with the #GameIds temp table in the bottom query I get a full table scan on some tables, making a longer execution time, and large memory grant warning. I thought a temp table would give a better…
Mike Flynn
  • 22,342
  • 54
  • 182
  • 341
0
votes
1 answer

Convert SQL code (as string) into table - dynamic pivot

I have used the below SQL script to perform dynamic pivot. I want to use the result to left join on another table, but the "INTO" method does not seem to work for the table variable @query. It shows an error: Must declare the table variable…
cheunghm4532
  • 45
  • 1
  • 7
0
votes
2 answers

Weird SQL Server lazy loading of table variables?

I came across a misleading error in SQL Server 2008 and I wonder if anyone can explain what's happening to me? I have a stored procedure something like this: declare @cross_reference table ( context varchar(20) not null , value1 …
Ray
  • 3,468
  • 8
  • 26
  • 27
0
votes
2 answers

Using select from a table variable in a LIKE condition

I have a server log file that I am trying to pare down for easier inspection. The table I am searching through is from just doing a bulk insert into a single column table named Spam.LogData. One big part of doing this is removing activity of…
Ryan Gilbert
  • 3
  • 2
0
votes
0 answers

SQL Table Variable to take correct path if value in column 1 is 0 or Null

I need to make changes to database in bulk from information provided on a spreadsheet. At the moment the Table Variable values are added manually, but the hope is to do this automatically from an existing Excel front end (It's a Legacy Application…
dori2o
  • 31
  • 6
0
votes
1 answer

Can I have table variable in stored procedure in redshift?

We are planning to create a procedure for our logic what should be in PL SQL in redshift (using workbench). Can we use a table variable to traverse through the rows of the table ? Like we have dataframe in Python.
Shefali Arora
  • 1
1 2 3
…
11 12