CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alex Gordon
Alex Gordon

Reputation: 60741

passing a temporary table into another stored procedure

i would like to do this:

DECLARE @TmpTable TABLE = select * from someTable where someTable.Column1='BLAH'

i need @TmpTable to be a subset of someTable and i don't want to implicitly declare a structure with fields for @TmpTable, rather i want it to be dynamically created from someTable.

is this possible?

thanks so much for you help and guidance!

i've taken the suggestion to try the temporary table. however, i am getting the error:

  The table #SubSet does not exist in the database

here's the code:

DECLARE @StartDT DATE
DECLARE @MinDOS DATE
SELECT @MinDOS = MIN(dos) FROM accn_demographics
SELECT @StartDT = 
    CAST(CAST(datepart(YYYY,@MinDOS) AS varchar) + '-' + CAST(datepart(mm,@MinDOS) AS varchar) + '-' + CAST('01' AS varchar) AS DATETIME)
DECLARE @FileLocation VARCHAR(50)
DROP TABLE #SubSet
WHILE @StartDT < '20110901'
BEGIN
    SELECT * 
        INTO #SubSet
    FROM ViewAccountDetail
    WHERE datepart(yyyy,ViewAccountDetail.DOS) = datepart(yyyy,@StartDT)
        AND datepart(mm,ViewAccountDetail.DOS) = datepart(mm,@StartDT)
    SET @FileLocation='C:\test\'+'ViewAccountDetail'+cast(@StartDT as varchar)+'.csv'
    EXEC BCP_Text_File #SubSet, @FileLocation       
    SET @StartDT = DATEADD(MONTH,1,@StartDT)

    DROP TABLE #SubSet
END

Upvotes: 2

Views: 3748

Answers (3)

Alex Gordon
Alex Gordon

Reputation: 60741

here is what i did!

DECLARE @StartDT DATE 
DECLARE @MinDOS DATE 
SELECT @MinDOS = MIN(dos) FROM accn_demographics 
SELECT @StartDT = 
CAST(CAST(datepart(YYYY,@MinDOS) AS varchar) + '-' + CAST(datepart(mm,@MinDOS) AS varchar) + '-' + CAST('01' AS varchar) AS DATETIME) 
DECLARE @FileLocation VARCHAR(50) 
DROP TABLE #SubSet 
WHILE @StartDT < '20110901' 
BEGIN 
SELECT * 
INTO SubSet 
FROM ViewTransactionDetails 
WHERE datepart(yyyy,ViewTransactionDetails.DOS) = datepart(yyyy,@StartDT) 
AND datepart(mm,ViewTransactionDetails.DOS) = datepart(mm,@StartDT) 
SET @FileLocation='C:\test\'+'ViewTransactionDetails'+cast(@StartDT as varchar)+'.csv' 
EXEC BCP_Text_File SubSet, @FileLocation    
SET @StartDT = DATEADD(MONTH,1,@StartDT) 

DROP TABLE SubSet 
END

thank you everyone for your extraordinary help!

i went with a permanent table and just keep dropping it!

Upvotes: 0

Hector Sanchez
Hector Sanchez

Reputation: 2317

WITH A
AS
(
    select * from someTable where someTable.Column1='BLAH'
)

where A is the Alias of your "temporary table". After that you can select it like another normal table. Just know that you CAN'T make and Order By in the select * from someTable where someTable.Column1='BLAH'

Upvotes: 1

sam yi
sam yi

Reputation: 4934

select * 
into #temptable
from someTable 
where someTable.Column1='BLAH'

just change "@" to "#" ;) You can also use CTE for this as well. Link

Upvotes: 5

Related Questions