Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am aware of the concept of bulk insert wherein I load data from a flat file to a database.

Now I want to unload data from a table to a flat file, i.e. I want to export data in a table to a flat file. Can anyone tell me how to do this?

I am using SQL Server 2008 R2.

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

If the table is large, then BCP is the best choice.

Below script will bcp out the data in a flat file + you have to flexiblity of converting it to a stored procedure or schedule it using sql agent job as well.

/************************************************************************************************************************************************
Author      :   KIN SHAH    *********************************************************************************************************************
Purpose     :   Move data from one server to another*********************************************************************************************
DATE        :   05-28-2013  *********************************************************************************************************************
Version     :   1.0.0   *************************************************************************************************************************
RDBMS       :   MS SQL Server 2008R2 and 2012   *************************************************************************************************
*************************************************************************************************************************************************/

-- save below output in a bat file by executing below in SSMS in TEXT mode
-- clean up: create a bat file with this command --> del D:\BCP_OUT\*.dat 

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe
        +  QUOTENAME(DB_NAME())+ '.'                                    -- Current Database
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'            
        +  QUOTENAME(name)  
        +  ' out D:\BCP_OUT\'                                           -- Path where BCP out files will be stored
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
        +  REPLACE(name,' ','') 
        + '.dat -T -E -SSERVERNAME\INSTANCE -n'                         -- ServerName, -E will take care of Identity, -n is for Native Format
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'                     -- Optional to exclude any schema 
order by schema_name(schema_id)                         
share|improve this answer
add comment

Right click on the database, under "Tasks" look for "Export Data", here the selected database is preconfigured as "Data Source" on the next screen you define the "Destination", change this to Flat File Destination and go from there. Later on you can use this as a template for SSIS if you need to do it often.

share|improve this answer
    
How to do it using a stored procedure...? –  user1844638 Aug 5 '13 at 7:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.