How to export data from SQL server without using Import/Export wizard?

Question

Is there any way to export data from different schemas which resides in SQL server without using SQL Import & Export wizard ?

I have almost 400 tables which are split across different databases. Right now, I am using SQL Import & Export wizard where for each table, I have to open the SQL Import/Export wizard which does the export and would require to redo the same process for rest 399 tables & more.

Is there any SQL query or any auto process where we can export the data from different schemas in SQL server in one go?

Please help me on this, any pointers would be appreciated.

Thank you in advance.

If you simply need the data, check this out: https://stackoverflow.com/questions/51502844/bcp-from-one-table-to-another-table-in-different-servers-in-sql-server-2008-r2/51504615#51504615 if you need tables scripted.. google around for 3rd party tools? Nothing I know of built in for automation. — sniperd, Jul 27 '18 at 15:58

score 1 · Accepted Answer · answered Jul 27 '18 at 16:09

1

You can use bcp utility (more info here).

Here is a simple command to export the data in csv format from a table [SCHEMA_NAME].[TABLE_NAME] from the database [DB_NAME]:

bcp [DB_NAME].[SCHEMA_NAME].[TABLE_NAME] out C:\data.csv -c -T -t,

answered Jul 27 '18 at 16:09

Andrea

11,801
17
65
72

score 1 · Answer 2 · answered Jul 27 '18 at 22:36

Check Export table from database to csv file answer here:

Using SQLCMD (Command Prompt):

From the command prompt, you can run the query and export it to a file:

sqlcmd -S . -d DatabaseName -E -s',' -W -Q "SELECT * FROM TableName" > C:\Test.csv

Notes:

This approach will have the "Rows affected" information in the bottom of the file, but you can get rid of this by using the "SET NOCOUNT ON" in the query itself.
You may run a stored procedure instead of the actual query (e.g. "EXEC Database.dbo.StoredProcedure")
You can use any programming language or even a batch file to automate this

Using ApexSQL tool:

Export SQL Server data to CSV by using the ApexSQL Complete Copy results as CSV option.

The Copy code as is a feature in ApexSQL Complete, a free add-in for SSMS and Visual Studio, that copy the data from the Results grid to a clipboard in one of the following data files: CSV, XML, HTML in just one click.

In a query editor, type the following code and execute:

Example:

USE AdventureWorks2014

SELECT at.* FROM Person.AddressType at

In the Results grid, select the part or all data, right click and from the context menu, under the Copy results as sub-menu, choose the commands like CSV, XML & HTML

This will copy the selected data from the Results grid to the clipboard. Now, all that needs to be done is to create a file where the copied data should be pasted:

The ApexSQL Complete Copy code as an option can save you a great amount of time when you need to copy repetitive SQL data to another data format.

How to export data from SQL server without using Import/Export wizard?

2 Answers2