BCP with Double Quotes for OUTPUT File

Posted: December 19, 2013 | Author: | Filed under: SQL Server, SQL Server 2008 | Leave a comment 
 
 ----bcp OUT  with double quote around text type columns and ignore number columns
exec master..xp_cmdshell  'bcp "select  id, char(34)+col1+char(34), char(34)+col2+char(34), char(34)+col3+char(34) from  [yourDB].[dbo].[yourtable]" QUERYOUT "C:\temp\yourFile.txt"   -T -c -t, -b 100 -S serverName\DBInstance';

go

I have to add to this if you can use SSIS package (Simply Import/Export Wizard from SSMS). You can simply add a Text Qualifier through flat file connection setting with a double quote when you export your table to a CSV file. That is simple.

BCP — Another Script with -b -S and Identity Column

Posted: December 17, 2013 | Author: | Filed under: SQL Server | Leave a comment 
 
--Use Master
--GO

--EXEC master.dbo.sp_configure 'show advanced options', 1
--RECONFIGURE WITH OVERRIDE
--GO

--EXEC master.dbo.sp_configure 'xp_cmdshell', 1
--RECONFIGURE WITH OVERRIDE
--GO
 

--Table A1
----bcp out with Identity column
exec master..xp_cmdshell  'bcp [myDB].[dbo].[A1] OUT "C:\temp\myA1.txt" -T -c -b 10000 -S MyMachine\myInstance';

 go
----bcp in with all data
exec master..xp_cmdshell  'bcp [myDB].[dbo].[A1_bcp] IN "C:\temp\myA1.txt" -T -c -b 10000 -S MyMachine\myInstance';

go

When you use bcp to move data from a table with identity column, you can generate the script from source table for your target table. The identity value may change from your original values if you have gaps in your source. If you want to keep all values same, you need to turn off the identity feature in your target table.

Migrate Data Between Two Databases with BCP

Posted: December 12, 2013 | Author: | Filed under: SQL Server 2008 | Leave a comment 
 
 
------ bcp out
exec master..xp_cmdshell  'bcp [myDB1].[dbo].[theTable] out "C:\temp\theTabledata.dat" -T -c -b 100000';

----bcp in
exec master..xp_cmdshell  'bcp [myDB2].[dbo].[theTable] IN "C:\temp\theTabledata.dat" -T -c -b 100000';

It took 40 minutes to export the table to data file and it took 36 minutes to import these (over 323 million) rows.
The theTabledata.dat file size is about 37G. All indexes from target table are dropped before importing.
The -b switch is important to set a batch size.