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
0
votes
3 answers

Read comma separated values and insert in SQL Table in batches

I have 4 columns in a sql table Offer. id (PK auto increment), name , org , TourCode Varchar(MAX) TourCode contains comma separated codes e.g (AVG123,JGH12 etc ). I have 40000 TourCode available as comma separated values I have to write a query…
Ravish Kumar
  • 67
  • 1
  • 6
0
votes
2 answers

dynamic Select statement on declared table variable - SYBASE

I have one declared table variable in stored procedure,(sybase database). Data is populated in that table as needed. But now I want to select particular columns based on different conditions. I am trying dynamic SQL to do the same but not working.…
Abhijeet_Brbl
  • 1
  • 1
  • 3
0
votes
1 answer

How to use sp_execute passing table parameter

I need to pass a table var to sp_executesql statement. Do you know how can I pass the table variable to sp_executesql? Here is how I pass the regular variable (not table variable) to sp_executesql EXEC sp_executesql @statement, N'@Status…
Prime
  • 69
  • 2
  • 12
0
votes
2 answers

Stored procedure not returning results from table variable?

I have a stored procedure that I'm working on and I'm trying to get it to return the results that are stored into a table variable that I created within the procedure. For some reason, its not returning anything when I execute the procedure.…
Jake
  • 341
  • 1
  • 3
  • 12
0
votes
0 answers

T-SQL - Casting from nvarchar to int & matching to nchar

Using SQL Server 2008 R2 (SP1) on two separate database servers that are set up identically. Using already created tables and data, simplified for this example. The issue I am having is that one query returns data on both servers, and another query…
steve_o
  • 1,243
  • 6
  • 34
  • 60
0
votes
2 answers

UPDATE with WITH-AS clause

When I run the following script, Original_Table gets updated by the UPDATE statement WITH temp AS ( SELECT ROW_NUMBER() over (partition by x order by y) row_num, x, z FROM Original_Table ) UPDATE temp set z = a + (select…
Yevgeni Grinberg
  • 359
  • 5
  • 19
0
votes
0 answers

T-SQL Scripting data and schema from table variable or temp table

Using SQL Server 2012. I am aware of the ability in SSMS to script schema & data to a query window, file, etc. Right click on database, Tasks --> Generate Scripts --> Choose Objects --> Advanced Scripting Options --> General --> Types of data to…
steve_o
  • 1,243
  • 6
  • 34
  • 60
0
votes
1 answer

How to process output of multiple tables from SQL procedure?

I have a procedure that generates two tables as output, but it generates an error when I run it. I want to pass both tables to local variables. DECLARE @model_data_stats TABLE (var VARCHAR(150), center FLOAT, scale FLOAT); DECLARE @model_log_stats…
A. Stam
  • 2,148
  • 14
  • 29
0
votes
1 answer

Creating Table Variable and inserting 100,000 rows (how to bypass insert into 1000 records limit?)

Creating Table Variable to insert into Temporary Table. But I need to repeat my insert into @TableVariable VALUES every 1000 records. How to bypass insert into 1000 records limit? Current script is: declare @@globalVariable Table (ID int, LName…
Data Engineer
  • 795
  • 16
  • 41
0
votes
1 answer

Declare table variable in a UDF to enter table name as a parameter

I am working on a query and created a function to fetch result from outcomes table susing the following code. CREATE FUNCTION dbo.Shippad (@tbl NVARCHAR(30)) RETURNS TABLE AS RETURN SELECT LEFT(ship, Charindex(' ', ship) - 1) + ' ' …
PURWU
  • 397
  • 1
  • 8
  • 22
0
votes
0 answers

SQL 2012 Query to output to "table" column

I have prototype of SQL query (actual query is too huge to post) SELECT Site, Risk_Time_Stamp,COMPUTER_NAME, [IP_ADDR1_TEXT],Number_of_Risks FROM dbo.sem_computer WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID GROUP…
Rhonda
  • 1,661
  • 5
  • 31
  • 65
0
votes
1 answer

Table expression and table variable/temporary table persistence

From what I understand, table expressions do not persist, meaning every reference to it in the outer code is a repeated call to the definition. This is where table variable/temp tables comme into the picture, to persist the table and avoid…
Kenny
  • 1,902
  • 6
  • 32
  • 61
0
votes
1 answer

Replace column text from a list of reserved words

I have a table variable which contains a list of words that I want to replace from query results. I want to search for any of these 600 words in the table variable. I did 3 just for this example. Then, I want to replace the word if it's found. I'm…
JeffB
  • 57
  • 1
  • 1
  • 4
0
votes
1 answer

Sql Virtual Table with Read Only Access: insert, delete , update?

2 questions: Will i be able to create a virtual table using a user that has read only access to a database ? ex: DECLARE @tblStudentDtl TABLE( SrNo int IDENTITY not null, sName varchar(50) , sAddress varchar(100), ClassName varchar(100) ) Will…
Nathan
  • 24,586
  • 4
  • 27
  • 36
0
votes
2 answers

Physical Table being used a Temp Table in a warehosue. Any advantage?

As I stated in previous posts I've inherited a warehouse based on SQL Server framework. In my continuous review of existing components and the practices adopted by the predecessors I found something that caught my attention and surprised me: there…
G21
  • 1,297
  • 2
  • 18
  • 39
1 2 3
…
11 12