/*
date: June 2017
author: rchu
purpose: sql server bcp examples
*/


/* create a table to play with */
truncate table [dbo].[tempchu];

drop table tempchu;

create table tempchu(
custName varchar(150),
custAdr varchar(50))


/* Generate non-xml and xml format files from the tempchu table */
--create format file
bcp "[AdventureWorks2012].dbo.[tempchu]" format nul -f "C:\myscratch\tempchu.fmt" -T -c -t,
--create format xml file
bcp "[AdventureWorks2012].dbo.[tempchu]" format nul -c -x -f "C:\myscratch\tempchu.xml" –t, -T

/* use the non-xml format file to load data into the table */
bcp "[AdventureWorks2012].dbo.[tempchu]" IN "C:\myscratch\tempchu.csv" -c -f "C:\myscratch\tempchu.fmt" -T -S localhost

/* Query the table. Verify that new data was loaded correctly */
select * from [dbo].[tempchu];

/* Sample load and unload using bcp */
bcp.exe "[AdventureWorks2012].dbo.[tempchu]" out "C:\myscratch\tempchu.dat" -x -n "C:\myscratch\tempchu.xml" -T -S localhost

bcp.exe "[AdventureWorks2012].dbo.[tempchu]" in "C:\myscratch\tempchu.dat" -n -T -S localhost

/* Sample CSV data 
Rick Chu,1576 NW right
Kim Chu,2363 NW left
Britney Chu,2367 NW up
*/