bcp tutorial or example

• zwheeler

  Ten Centuries

  Points: 1136

  More actions

  August 11, 2011 at 11:02 am

  #242995

  Hi

  Does anyone have a very simple example or tutotial of using bcp to export a query

  thanks in advance

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 11, 2011 at 11:06 am

  #1367464

  you mean besides Books Online?

  http://msdn.microsoft.com/en-us/library/ms162802.aspx

  from my snippets:

  EXECUTE master.dbo.xp_cmdshell 'sqlcmd -Q "SELECT * FROM AdventureWorks.Person.Contact" -o MyOutput.txt'

  EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT FirstName, LastName FROM AdventureWorks.Person.Contact ORDER BY LastName, Firstname" queryout Contacts.txt -c -T'

  EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT object_name(object_id) As TbLName,name as ColName FROM SandBox.sys.columns ORDER BY object_name(object_id), column_id" queryout C:\Data\Objects.txt -t"|" -c -T '

  --only global temp tables can be exported, so move a results of a complex query into a global, then export

  Select PatientDisplayID as 'Client ID', ISNULL(SugarEntryCount,0) as 'BG Count',

  ISNULL(CarbsEntryCount,0) as 'Carb Count', ISNULL(MedsEntryCount,0) as 'Med Count'

  INTO ##PIPE_REPORT

  FROM #temp_patients

  EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM ##PIPE_REPORT " queryout C:\Data\Objects.txt -t"|" -c -T '

  DROP TABLE #temp_patients

  DROP TABLE ##PIPE_REPORT

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Brandie Tarvin

  SSC Guru

  Points: 173053

  More actions

  August 12, 2011 at 11:19 am

  #1368117

  Another place to check is the usual assortment of certification books. They often have tutorials you can walk through yourself in how to use the various tools in SQL Server.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• SGT_squeequal

  SSCertifiable

  Points: 7167

  More actions

  August 13, 2011 at 2:15 pm

  #1368350

  you can also use SQL to create the BCP syntax for a batch file

  BCP IN

  select 'bcp %DATABASE%.dbo.'+name+' in "'+name+'.bcp" -E -n -T -S%SERVER%' from sysobjects (nolock)

  where name like 'table_name%'

  and xtype='u'

  BCP OUT

  select 'bcp %DATABASE%.dbo.'+name+' out "'+name+'.bcp" -E -n -T -S %SERVER%' from sysobjects (nolock)

  where name like 'tablename%'

  and xtype='u'

  [/code

  then create a batchfile paste in the results of the above query and then at the top of the batch file type

  set database=database name here

  set Server=servername here

  ***The first step is always the hardest *******