2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
amad
  • 59
  • 1
  • 4
  • 1
    A temp table, a table variable, a permanent table, or an XML string. – Gordon Linoff Aug 18 '15 at 22:02
  • Michael, Chris, Gordon thanks all. i just read about variables in tsql. specifically temp tables, table variables , table-valued functions, derived tables, and common table expressions. which of these can i use to reference my row set in the same batch. Thanks – amad Aug 18 '15 at 22:44
  • A CTE is no good in the same batch - you can only use it _once_ immediately after. – Nick.Mc Aug 19 '15 at 05:03
  • Is this just a theoretical conversation, or are you actually trying any of these ideas? If you've tried them, which ones, and why didn't it work? – Chains Feb 22 '16 at 20:55
  • Can you clarify what you mean by 'multiple queries', or give some sample code for what you're trying to do? – Chains Feb 22 '16 at 20:59

4 Answers4

2

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
Bill Martin
  • 4,825
  • 9
  • 52
  • 86
  • Thanks Bill. i read that temp tables' scope is within a session. check this out : http://stackoverflow.com/questions/18614344/scope-of-temporary-tables-in-sql-server-2012 – amad Aug 19 '15 at 03:53
  • @amad -- are you saying this answer doesn't work for you? – Chains Feb 22 '16 at 20:56
0

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

Carlos Barini
  • 129
  • 1
  • 4
0

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)

Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
-1

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.

Tony
  • 74
  • 2