Export data from Sql Server to excel

Hi All,
   I am looking  a fast option to transfer large data from Sql Server 2016 . I have tried bcp and some  export happens . But I am not able to view data . Also for bcp, in command prompt, It prompts for entries. How to get rid of these manual prompts?

Can you please give an example for bcp ?

I followed the link for bcp
https://blog.sqlauthority.com/2016/09/30/sql-server-simple-example-bcp-command-line-utility/
Sam OZAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
Have you tried Import / Export wizard from within SSMS already? Also if you could quantify 'large data', please?
Sam OZAuthor Commented:
Yes, I tried import/export and it is slower than  what I need for a specific time critical task .
Large data is 2 million records and one field with Filepath in it .

Apart from all these, all I am looking is an example to use bcp ( Preferably one where I can run a query. But fetching data from a table or view  is good enough)
Vitor MontalvãoMSSQL Senior EngineerCommented:
Also for bcp, in command prompt, It prompts for entries.
What he prompts for?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Sam OZAuthor Commented:
I get prompt, Enter file storage type of field
I can live wih the prompt. But what troubles me is the output is not opening in excel

I would be thankful if anyone used bcp can just give an example that works to write to excel
Mark WillsTopic AdvisorCommented:
If running from within SQL Server, theres a couple of steps.

1) show advanced options
2) enable xp_cmdshell
3) create your sql script
4) run the bcp command
--step 1 turn on xp_cmdshell

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

EXEC master..xp_cmdshell 'bcp "select yourcolumns from yourdatasource" queryout "Your output destination.csv" -T -t, -c -CACP'

-- maybe turn off xp_cmdshell again

EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE

Open in new window

Parameters for bcp in the above are -T -t, -c -CACP
-T = trusted connection
-c = character mode
-t, = -t means delimited by the next character ie comma delimited
-CACP is the codepage to use

-S is sometimes used (and is needed for SQL Express) to tell BCP what server\instance  
If not using a trusted connection, the use -Uusername - Ppassword instead of -T
And yourdatasource would need to be in the full naming convention of DB.SCHEMA.TABLENAME - bcp is a command line utility and wont know where your data lives.



Have a read : https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-and-export-bulk-data-by-using-the-bcp-utility-sql-server
and more detailed about BCP itself : https://docs.microsoft.com/en-us/sql/tools/bcp-utility

If you can tell us what you are doing in a bit more detail, we can provide more guidance

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Sounds like you are running from the command line. You need to read the BCP link above...

but essentially you will type in the command line prompt the equivalent of
bcp "select yourcolumns from yourdatasource" queryout "Your output destination.csv" -T -t, -c -CACP

Open in new window

and will most likely need to tell it what server using the -S paramter
Sam OZAuthor Commented:
Sam OZAuthor Commented:
Sorry, I did not see Mark Wills answered the question. That is the solution and I can close the question giving all points to Mark Wills
Mark WillsTopic AdvisorCommented:
Thanks Sam, will tidy up for you and appreciate your kind words and consideration.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.