CODEWITHSUNDEEP
sqlsql-servert-sql
amad
amad

Reputation: 59

T-SQL Variables

What type of objects can i use( in T-SQL) if i want to reference my row set from multiple queries in the same batch?

i am emphasizing on the same batch.

Thank you so much

Upvotes: 2

Views: 360

Answers (4)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23098

The question is not completely clear for me, but one way is to use is to materialize the data in a "session" table:

SessionId - UNIQUEIDENTIFIER is one option
Col1
Col2
...
Coln

UNIQUEIDENTITIER is the easiest option, but not the fastest (large key). Otherwise, a SEQUENCE (SQL2012+) can be used.

  1. Generate a session identifier
  2. Queries that need to process the data receive this identifier and filters data using it.
  3. The solution allows multiple writers and readers "in the same time"
  4. Table deletion (truncation) can be done during system downtime (if possible), to minimize deletion impact (deletion is a heavy operation and locks the entire table)

Data generation is obtained using INSERT .. SELECT:

INSERT INTO SessionTable
(SessionId, Col1, Col2, ... , Coln)
SELECT @SessionId, ....
FROM <various sources>

Disadvantages:

  1. table is materialized and accessible to everyone with SELECT right. Security (DENY) must be handled, if sensitive data is handled.

  2. table must be truncated/dropped explicitly (temporary tables and table variables are automatically dropped)

Upvotes: 0

Carlos Barini
Carlos Barini

Reputation: 129

It was not clear enought to me, but you can use bulk insert, or bulk update to handle multiplus results.

To INSERT, you can do a SELECT returning exactly the fields to be inserted on another table like...

INSERT INTO TableA (ID, Name, Phone) SELECT CustomerID, CustomerName, CustomerPhone FROM TableB

To UPDATE, realize you have TableA with 'customers' to be updated with TableB informations, then you can do...

UPDATE TableA SET TableA.Phone=TableB.Phone FROM TableB WHERE TableA.CustomerID=TableB.CustomerID

Upvotes: 0

Tony
Tony

Reputation: 74

You might want to use a Global Temp table, as this has scope outside the current session, but be warned it is available only whilst there is a session accessing it.

Upvotes: -1

Bill Martin
Bill Martin

Reputation: 4943

Create a temp table variable and insert into it. 

DECLARE @TempCustomer TABLE
(
   CustomerId uniqueidentifier,
   FirstName nvarchar(100),
   LastName nvarchar(100),
   Email nvarchar(100)
);
INSERT INTO 
    @TempCustomer 
SELECT 
    CustomerId, 
    FirstName, 
    LastName, 
    Email 
FROM 
    Customer
WHERE 
    CustomerId = @CustomerId

Upvotes: 2

Related Questions