outputting data from temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

outputting data from temp table

Author

Topic

cjp
Yak Posting Veteran

69 Posts

Posted - 2010-06-14 : 04:32:17

I am using SQLS 2005 SP 2.

I would like to be able to query data into a temp table and then output the result straight into a text table on a backup disk. I have been trying to use bcp to output the content of the temp table but receive an error message to the effect that I am referring to an invalid object name. I have tried querying out the system name for the temp table and using this but it still throws an error.

Here is what I am trying to do:

Select *
into #practices
From thinprac /*this contains the names of practices contributing medical data to our work*/

GO

EXEC xp_cmdshell

'bcp "SELECT * FROM [tempdb].[dbo].[#practices]" queryout "C:\bcpoutput\practices.txt" -T -c'

GO

If I replace the first stage with a formal make table query, the bcp stage works fine, but I would like to be able to do this straight from a temp table since it is faster and since it means that I am not storing unnecessary objects in the database.

I have tried and failed to find an answer in BOL and elsewhere on the web - can someone set me on the right path?

Thanks.

Chris

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-14 : 04:44:04

Can you make a stored procedure out of the query instead and skip the temp-table entirely? And then just bcp the results of the procedure to the text-file instead? Unless this "Select * into #practices From thinprac" is a placeholder for a bunch of inserts and updates and such I don't really see the need for a temp-table...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

cjp
Yak Posting Veteran

69 Posts

Posted - 2010-06-16 : 03:19:38

Thanks for the suggestion. I tried this but couldn't get it to work. However, I have found an answer to my original question: the solution is to treat the temp table as a global object, and bcp will then recognise it. For example:

Select *
Into ##prac
From practices

GO

Exec xp_cmdshell

'bcp "Select * From ##prac" queryout "C:\tests\bcp_pracs.txt" -T -c -a32768'

GO

/*network packet size = 32576*/

Using two hashes rather than one to introduce the name of the temp table does the trick. I have set the packet-size switch (-a) since experiment shows that the procedure completes more quickly with a higher packet-size (this observation is ratified in BOL). My system was happy with this but would not allow -a65535 which, according to BOL, is the maximum packet size that is permitted; 32576 is the highest I could achieve in real use.

Chris

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-16 : 03:47:07

What was it that you couldn't get to work with the stored procedure method? Writing data to a temp-table doesn't make much sense (at least not to me) when all you're using it for is to run a select * without any further modifications. But I might not see the full picture here...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

cjp
Yak Posting Veteran

69 Posts

Posted - 2010-06-16 : 04:52:22

I will try to answer your question.

1 - I am self-taught and from a non-computing background. This means that I have developed quite simple procedures to do relatively complex jobs. I have never used stored procedures before since I document all my code, study by study, and then re-use procedures: this always works because studies change but object names remain identical, so re-use is easy.

2 - Following your suggestion, I looked up SP's and managed to write some that worked - but they simply generated tables that I then had to bcp in order to save them as text files.

3 - To this point, I have output data for statisticians by writing make table queries, exporting the results to a folder on my hard disk and then either burning these to an optical drive or copying them to a removable usb drive - this is how I provide data for analysis in a medical stats program.

4 - I wanted to avoid the business of saving output tables within SQL Server and then exporting a copy as a text file to a folder on my hard drive...and then copying this to an optical drive. So I have tried to develop a method that generates usable data without relying on a query that first saves data as a table in SQL Server. Writing a make table query into a temp table and then bcp-ing straight to a portable usb drive accomplishes this purpose (although I still have to develop a method of persuading bcp to provide field names in the output - I am working on some simple ideas for this since I don't understand the complex solutions that are published on the web).

Using a temp table + bcp means (a) that the table will be deleted when I switch off so I am not needlessly increasing the size of the database and (b) that the procedure is much quicker than my previous method.

I don't provide SQL solutions for other people - mine is a standalone installation from which I generate output tables in text format that statisticians can use. I hope this makes some sort of sense.

Chris

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-16 : 05:18:20

Ok, thanks for the info. I just mocked up this example to illustrate how you could do this without using a temp table and it works quite nicely:

CREATE TABLE practices (id int, name varchar(20))
GO
INSERT INTO  practices 
SELECT 1, 'henning' UNION ALL SELECT 2, 'frettem'
GO
CREATE PROCEDURE dbo.MyStatisticsReport 
AS
BEGIN
	SELECT * FROM practices 
END
GO
EXEC dbo.MyStatisticsReport 
GO
EXEC xp_cmdshell 'bcp "EXEC mydbname.dbo.MyStatisticsReport" queryout "C:\temp\bcp_pracs.txt" -T -c -a32768'
GO

Now the column names in the output file is sort of a problem with bcp...I think the best/easiest way would be to do something like this:

SELECT 'Firstname', 'Lastname', 'Amount'
UNION ALL
SELECT Firstname, Lastname, CAST(amount as varchar(50)) FROM table

But by doing it this way you'd have to CAST all columns to a varchar datatype which can be sort of a hassle...but it works.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

cjp
Yak Posting Veteran

69 Posts

Posted - 2010-06-17 : 04:39:02

Thanks for your example. I can see that your method is likely to work, so I will try it out this morning. The acid test will be to run a version of the SP method on a large table - something too large to fit into memory.

I have managed to develop a simple method for persuading bcp to fetch field names - so, progress is being made.

Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources