Exporting resultset of temp table to other formats. How?

Question

Exporting resultset of temp table to other formats. How?

MuditGupta

SSCrazy

Points: 2596
More actions
November 12, 2008 at 10:55 am

#383362

Hi there,
Is there a way to export result set of temporary table to another format like flat file, Access etc?
If so, how can I do it?
Like using SQL server import/export wizard or something?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

My kneejerk response was BCP, but you're talking about a temporary table. Is the file small enough that you could just copy it briefly to a "permanent" table, then launch BCP and delete it afterwards?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

MuditGupta SSCrazy Points: 2596 More actions · Answer 2

that's the problem. These resultsets are huge..

I also thought about dumping result into non-temp table and then performing desired transformation. But for big result set, i don't know if its advisable

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

OK... I'm assuming you need the result set for subsequent processing, not just generating it for a download. (If that assumption is wrong and all you want to do is download, BCP can run the query for you, but I'm guessing you know that already.)

Back when computers were still hand-cranked, I learned how to code on an IBM System 34. Having no tempDB, we had to put a scheme in place to create "permanent" files, that we could easily recognize and get rid of if job steps failed or a users session ended unexpectedly. That concept might be applicable here, but you will have to accept doing everything in dynamic SQL.

What follows isn't pretty, maybe somebody else will have a better idea.

Good luck 🙂

Bob

-----------------------------------------------------------------------------------------------------------------

DECLARE @tabNamevarchar(100)

DECLARE @dynSQLnchar(4000)

set @tabName = replace('TEMP_'+cast(newid() as varchar(50)),'-','')

select @tabname -- you might want to save this in a log table with creation datetime

-- create the table

set @dynSQL = 'Create Table '+@tabname+' (ID int primary key, filler varchar(100))'

EXEC sp_executesql @dynsql

-- replace this select with steps to populate the table, process it, and BCP it

set @dynSQL = 'Select * from '+@tabName

EXEC sp_executesql @dynsql

-- drop the table

set @dynSQL = 'Drop Table '+@tabName

EXEC sp_executesql @dynsql

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

If you put all your stuff to create the result set in a temp table with no other output, not even rowcounts, and do a final SELECT from the TempTable at the end of the proc, then your BCP can call the proc as a query and it should work just fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Doh! :w00t:

I knew there had to be a better way. What I really like about this approach is that you could use it for table variables as well as temp tables, attach final states of variables, pull in data from joined tables that might have effected your processing, etc. Thanks, Jeff.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

MuditGupta SSCrazy Points: 2596 More actions · Answer 6

Bob & Jeff,Thanks!

I'll try these methods. thank you

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

sql-oholic (11/13/2008)
Bob & Jeff,Thanks!
I'll try these methods. thank you

Thanks for the feedback. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Bob Hovious (11/13/2008)
Doh! :w00t:
I knew there had to be a better way. What I really like about this approach is that you could use it for table variables as well as temp tables, attach final states of variables, pull in data from joined tables that might have effected your processing, etc. Thanks, Jeff.

... and do headers and footers with rowcounts like so many people seem to ask for in 1 file instead of 2.

Thanks for the feedback, Bob.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MuditGupta SSCrazy Points: 2596 More actions · Answer 9

I tried using sproc in BCP..but i am getting an error saying 'invalid object name'

Am I missing some trick? I read in some forums.. about SET FMTONLY ON/OFF

btw, this is my code:

CREATE PROCEDURE sprocBCP01

AS

Create table #temp01(COL1 char(2));

insert into #temp01(COL1)

( SELECT * FROM TABLE)

SELECT * FROM #temp01

The above code is fine.. it runs in SSMS.

I am using following as input in CMD:

bcp "EXEC DB.dbo.sprocBCP01" queryout "C:\bcptest01.txt" -c -T

MuditGupta SSCrazy Points: 2596 More actions · Answer 10

if i change temp table to global temp table, it works.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

sql-oholic (11/13/2008)
if i change temp table to global temp table, it works.

It's been a while since I've had to do such a thing, but that just doesn't seem right to me... I'm gonna have to play with it a bit...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)