CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlstored-procedures
Venki
Venki

Reputation: 57

How to Export Stored Procedure Output to Text file

I need your support to come up with the solution.

I have 10 different set of stored procedures which I have to export into text file, all 10 procedures will return the same set of columns (only calling parameters are different).

I am not getting the solution how to do ?

Could you please help me to understand how to export the data from a stored procedure output to tab delimited text file?

Also if possible can tell me in SSIS package too.

Thanks

Upvotes: 0

Views: 7411

Answers (1)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

Simple solution here.

For each SP:

  • execute SP in Management Studio
  • underneath in Results Pane, right-click and choose Save Results As...
  • under Save As Type select CSV

At the end, open each file in notepad and simply concatenate results.

EDIT

If you have e.g. 10 stored procedures, you could do the following:

  • create temporary table for output 

    CREATE TABLE #output (...)

  • use 10 INSERT...EXEC's to fill temp table

    INSERT INTO #output EXEC MySP1 ...
GO
INSERT INTO #output EXEC MySP2 ...
GO
INSERT INTO #output EXEC MySP3 ...
GO
...
INSERT INTO #output EXEC MySP10 ...
GO

  • execute query SELECT * FROM #output

  • underneath in Results Pane, right-click and choose Save Results As...
  • under Save As Type select CSV

EDIT 2

If you wish to have an information in your output temp table where you data came from (which SP), you may add another column to your SP, in order to have a bit of control over INSERT...EXEC statement.

If you wish to do that but can't control the SP output, one way to do it is to control the default value of #output and another is to dd another temp table for intermediate results. I would use the second one:

  • create temporary table for output, with e.g. your table name as identifier.

    CREATE TABLE #output (spname sysname, ...)

  • create temporary table for intermediate result

    CREATE TABLE #temp (...)

  • use 10 INSERT...EXEC's to fill intermediate and then output table

    TRUNCATE TABLE #temp
INSERT INTO #temp EXEC MySP1 ...
GO
INSERT INTO #output
SELECT spname = 'MySP1', * FROM #temp
GO

TRUNCATE TABLE #temp
INSERT INTO #temp EXEC MySP2 ...
GO
INSERT INTO #output
SELECT spname = 'MySP2', * FROM #temp
GO
...

  • execute query SELECT * FROM #output

  • underneath in Results Pane, right-click and choose Save Results As...
  • under Save As Type select CSV

Upvotes: 3

Related Questions