174 questions
0
votes
1
answer
47
views
DAX cumulative open projects
I ask for your help with the feeling of despair after having tried to ask help internally in my company and tried all the IA models out there without success for my problem.
So, basically, i want to ...
1
vote
2
answers
151
views
Efficiently mark and retrieve rows
I'm working on a SQL Server query where I need to perform the following tasks efficiently:
Execute a complex and resource-intensive SELECT statement to retrieve a subset of rows.
Update same table ...
- 31
0
votes
1
answer
468
views
Saving a Collection within a Table PowerApps
I'm building an app on PowerApps, its a form that is normally completed on paper.
I've built the app and have the functionality of it working within the canvas but trying to save the collected ...
- 31
3
votes
1
answer
138
views
If table variables only have the scope of their batch, then why can I select from one after using GO?
I clearly misunderstand the difference between a batch and a session. I believe three things that cannot all be true at the same time:
It is very well known that table variable only have the scope of ...
- 1,764
1
vote
1
answer
376
views
Processing data row by row while bulk insert using JSON in SQl server
Need to Generate Tag Number While Inserting Data using JSON in SQl Server Stored Procedure.
I have 2 Tables
Table1: Category (lastusedIndex null in initial stage)
Id
categoryname
assetPrefix
...
- 327
1
vote
1
answer
726
views
SQL Server: Cardinality estimation for table variable
SQL Server does not maintain statistics for table variable and it always estimates 1 output row. Microsoft changed this behavior post release of SQL Server 2019. Though I am not able to find much ...
- 724
0
votes
0
answers
324
views
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 ...
0
votes
2
answers
595
views
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:
...
- 51
-2
votes
1
answer
705
views
I am facing a weird problem when inserting data from an SQL Stored procedure into a table variable
I have this stored procedure:
Lemma 1: exec GetData 1, '20220301', 1
I run it with 1 as one of the parameters, It does correctly return some data
Lemma 2: exec GetData 1, '20220301', 15
I run it with ...
- 122
0
votes
0
answers
476
views
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), @...
0
votes
0
answers
551
views
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 ...
- 23.4k
1
vote
0
answers
33
views
How to execute the Update method using a variable to call the table? [duplicate]
I'm trying to create a procedure in SQL Server to allow me to update a column on a table.
However, I want the table name to come from a variable instead of being given by me, but I get an error ...
- 11
-1
votes
1
answer
297
views
SQL query with Table variable/Temp Table - which one good in performance
SQL Questions:
Is this insert query is good in performance ?
UserIds List is variable, its length mybe 10, 20 ... till 500.
WITH Users
AS (SELECT [Id] from [electro].[User] Where [Id] IN (4438,...
- 26
0
votes
1
answer
258
views
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. ...
- 67
0
votes
2
answers
939
views
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 ...
1
vote
2
answers
286
views
Trying to use a variable in a SQL WHERE IN statement
I'm trying to use a @List Table to feed a Where IN statement. I keep getting the error, "Must Declare the scalar variable "@List".
I know I can simply use the select statement in the ...
0
votes
0
answers
188
views
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 ...
- 31
0
votes
1
answer
2k
views
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 ...
0
votes
1
answer
436
views
Oracle put resultset into variable in FORALL
I have the following plsql block
declare
TYPE t_mds_ids IS TABLE OF mds.id%TYPE;
l_mds_ids t_mds_ids;
l_mds_parents t_mds_parents;
begin
SELECT id BULK COLLECT INTO l_mds_ids FROM ...
- 393
0
votes
1
answer
277
views
Must declare the table variable error while creating a TVP function
I have the following function created with a TVP. I am inserting data initially into the TVP and then selecting from it.
CREATE FUNCTION [dbo].employees_data(
@employeeIds NVARCHAR(MAX)
)
RETURNS ...
- 1,901
0
votes
0
answers
269
views
How to update table variable in stored procedure from another user defined function in SQL Server?
Scenario: user-defined function returns one table.
If that table contains at least one row, one column in the table variable from another stored procedure should get updated.
Should I use cross join ...
0
votes
1
answer
161
views
Assigning TABLE typed variables in T-SQL
I've defined a user defined Table type - call it TrackRefsTable
Having declared two variables
DECLARE @FOO1 AS TrackRefsTable
DECLARE @FOO2 AS TrackRefsTable
Is there any way to set one to t'other? ...
user6499401
1
vote
1
answer
682
views
Different results while inserting into temp table and table variable
When I'm using a table variable to store queried result like this:
INSERT INTO @sortedArticleIds
SELECT article_id, NULL AS groupBy
FROM #articleIds a
GROUP BY article_id
ORDER BY MIN(...
-1
votes
1
answer
1k
views
Sending the stored procedure output as comma-separated rather than multiple rows
I have a stored procedure that returns multiple rows with 5 columns:
SELECT Travel, ID, Dept, Role, Country
FROM TravelManager
For example, I get:
DName ID Dept Role Country
-----------------...
- 5,317
0
votes
2
answers
877
views
Conditional IF Exists Insert
I have a query to first check if something exists. If yes, insert something into a table variable. Otherwise, insert something else into the same table variable.
IF NOT EXISTS (SELECT 1 FROM @Main ...
- 693
1
vote
1
answer
314
views
Inner join two table variables and return an output cursor from an anonymous plsql block in a c# application
I have this scenario:
I need to insert the result of many joins (15 tables with various filters) into a table variable var_TB_PROJECT
Then insert the result of many other joins (8 tables with various ...
- 61
0
votes
1
answer
1k
views
PLSQL ORACLE : Inner join between table variables
I need to create two table-type variables in oracle and make inner join between them.
I can't create temporary table in the source database because I dont have privileges.
How to create an anonymous ...
- 61
1
vote
2
answers
3k
views
How to create a sql server table variable in a query using sqlalchemy in python
I'm trying to create a table variable in SQL Server, query it, and return the results to a pandas dataframe (see example). I want to do this so that I can aggregate data in the database prior to ...
user845888
1
vote
1
answer
4k
views
How to use table variable in dynamic sql? OR create temporary table from user defined table type?
I am using SQL Sever 2016 and I have created user-defined table-type as below:
CREATE TYPE [dbo].[UDTT_Items] AS TABLE(
[ItemId] int identity(1, 1),
[ItemCode] [varchar](10) NULL,
[...
- 2,180
0
votes
2
answers
108
views
How to use a Table Variable with this specific T-SQL query?
I am using SQL Server 2012 and I have the following T-SQL query running against a table on my database. The query runs fine but my issue is that I want to get an output for several specific dates. ...
- 4,493
1
vote
3
answers
3k
views
Using CONTAINS to find items IN a table
I'm trying to write a SP that will allow users to search on multiple name strings, but supports LIKE functionality. For example, the user's input might be a string 'Scorsese, Kaurismaki, Tarkovsky'. ...
- 749
1
vote
1
answer
4k
views
Input and output a table from a function in SQL
I would like to create a function in T-SQL which both inputs and outputs instances of a user-defined table type. However, on the line of the RETURN statement, it is throwing an error:
Must declare ...
- 91
0
votes
1
answer
719
views
SQL Server / Delphi : how to transfer a list of integers as a parameter using a table variable type?
I'm using DELPHI XE6 with FireDAC components and has a SQL Server database server.
In the database, I have a stored procedure that takes two parameters, one integer and then a table variable that ...
- 841
2
votes
3
answers
1k
views
"Must declare the scalar variable" when trying to run UPDATE statement in SQL
Trying to update two tables (City and Location) using two table variables (@NameZip, @NameZip2). The city names have ZipCodes and ZipCodes have Names instead of vice versa. Updates are changing City ...
- 85
0
votes
1
answer
585
views
Adding new data to the existing tables of a database in SQL Server using table variables
I'm trying to add new rows to the existing tables in the database by using table variables. The problem I'm facing is due to the auto-increment primary key in the database. The tables are like below:
...
- 85
0
votes
1
answer
1k
views
Why are my Memory-Optimized Table Variables Using Disk Space
I have created a memory optimized table type in my database like so:
create type tblLocation_InMem as table(
IndexLocation int not null index IX_LocInMem,
IndexRootLocation int not null,
...
- 4,487
0
votes
0
answers
116
views
Can table "variables" be assigned in SQL Server?
Suppose I have a table variable defined as:
DECLARE @FilteredIDs TABLE(ID UNIQUEIDENTIFIER, UNIQUE CLUSTERED (ID))
Functions can return table variables, so supposed I have a function that accepts ...
- 19.3k
2
votes
1
answer
247
views
Performance bottleneck for SQL Table-Valued Function for CSVtoTable
I am working on a query where I need to convert a CSV value holding few ID's sepearated by ,.
Currently I am using a Table-Values Function taken from here:
SET ANSI_NULLS ON
GO
SET ...
- 1,421
-1
votes
1
answer
345
views
What is that makes temp tables more efficient than table variables when working with large data?
In SQL Server, the performance of temp tables is much better (in the means of time) compared to table variables when working with large data (say inserting or updating 100000 rows) (reference: SQL ...
- 1,806
0
votes
0
answers
257
views
Create Table Variable only when there is rows to insert in it
In Query that haves 3 tables variables joined with 2 tables in some cases where those variable tables are empty (no rows) in the Execution Plan still appears with 16% cost for the scan of each table ...
- 618
3
votes
3
answers
6k
views
Join 2 Table Variables in SQL
How to join 2 table variables which are not consists of a foreign key column.
DECLARE @InventoryIDList TABLE(ID INT)
DECLARE @ProductSupplierIDList TABLE(ID INT)
Excepted output
@InventoryList
------...
- 3,386
1
vote
0
answers
121
views
Locking of temp table in SPROC from multithreaded application
I have a scenario where a Multithreaded .net application calls a stored procedure which in turn uses a temp table in it.
In our application concurrently we are calling four threads which ...
- 555
0
votes
3
answers
593
views
How to use an And-Or as a variable
I'm actually trying to get the data from a table but I wanted that "And/Or" changes based on the table. I'm using SQL Server.
I'm into something like this:
DECLARE @TURNOS TABLE (ID INT IDENTITY(1,...
- 107
0
votes
1
answer
352
views
Sql Server 2016 Temp Table or Table Variable inside WHILE Loop
I have a while loop, that will run 300M times.
Each time this loop runs, it creates a 1 row table with two columns, which in the end of the loop this table is deleted and once the loop re-starts, ...
- 85
1
vote
1
answer
2k
views
Must declare the scalar variable when referencing a table valued parameter
This question follows from this one.
The following SQL works:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Update_Repair_Details]
@RepairID BIGINT,
@NewDetails ...
- 537
0
votes
1
answer
3k
views
Declare and set a variable with more than one possible value
Is it possible to set a variable to have more than one value?
In the example below, I would like to set the @variable and use store numbers 1,4,7 and 12
And then use the variable in the WHERE ...
- 1,040
0
votes
3
answers
3k
views
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 ...
- 67
5
votes
1
answer
6k
views
Why can't I run INSERT EXEC on a table variable in a T-SQL function?
My function looks like this:
CREATE FUNCTION fn_FileSys_DirExists(@dirName AS nvarchar(260))
RETURNS bit
AS
BEGIN
DECLARE @dirExists int
DECLARE @fileResults TABLE
(
file_exists ...
- 51.4k
0
votes
2
answers
4k
views
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 ...
1
vote
0
answers
396
views
TSQL: Loop through a table by given time intervals and count
Source table
dbo.sourcetable
The table has more columns than shown here:
| ID | TrackingID | TrackingTime |....
|--------|----------------|-----------------------|
| 001 | ...
- 113