......BCP (BULK Export & Import)......

BCP COPY To BCP IMPORT, BULK INSERT in SQL Server

Note : -  BCP Queries can not run in wrapping text,  run in one line statement.

--START-----Exporting  format file using fmt extension-------FORMAT file in text format----

EXEC xp_cmdshell 'bcp DataBaseName..TableName format nul -n -f C:\Path_n.fmt -S ServerName\ServerName_2008 -T' 

---END----Exporting  format file using fmt extension-------FORMAT file in text format--------

--START-----Exporting  format file using fmt extension-------FORMAT file in XML format --

EXEC xp_cmdshell 'bcp DataBaseName..TableName format nul -c -t~ -f D:\BCP_FormatFiles_CSVData\FileName.xml -S ServerName\ServerName_2008_2008 -x -T'

 
---END----Exporting  format file using fmt extension-------FORMAT file in XML format

---START-------Exporting data with format in csv file -----OUT Command----------------

EXEC xp_cmdshell 'bcp TableName..Databasename out D:\BCP_FormatFiles_CSVData\_Records.csv -f D:\BCP_FormatFiles_CSVData\QDeals.xml -S ServerName\ServerName_2008 -T -L 100 -r'

 ---END-------Exporting data with format in csv file -----OUT Command------------------

---START--------Importing data with format in csv file ----IN Command ----------------------

EXEC xp_cmdshell 'bcp Master..BCPTableNoteDeals in D:\FileName_06Jul2015_112447.csv -f C:\PersonFormat_c.xml -S ServerName\ServerName –T'

---END--------Importing data with format in csv file ----IN Command --------------------

Note:- Bulk insert command can allow for wrapping query text  but BCP not Allowed  

---START-----BULK INSERT Using CSV--XML------Files ----------------------------

BULK INSERT Master..BCPTableNoteDeals
FROM 'D:\BCP_FormatFiles_CSVData\QDeals_Records.csv' WITH (FORMATFILE='D:\BCP_FormatFiles_CSVData\QDeals.xml');
GO

---END-----BULK INSERT Using CSV--XML------Files -------------------------------

---START-----Query_OUT Command in Where Clause-----------------------------------
SET @DatabaseName ='DatabaseNametablename'   
SET @minpo = N'Where clauser'
SELECT @sql = 'bcp "SELECT * FROM DatabaseNm.dbo.TablNm where Note_Product_Name= ''' + @minpo + '''  " queryout d:\test.txt -c -t, -T -S ' + @@servername
EXEC master..xp_cmdshell @sql
---------------------------------------------------------------------------------------------
DECLARE @sql111 VARCHAR(8000)
SELECT @sql111 = 'bcp " SELECT * FROM DB.dbo.TBN " queryout d:\NewNoteMastertxtImport.csv -c -t~ -T -S ' + @@servername
EXEC master..xp_cmdshell @sql111
----------------------------------------------------------------------------------------------
---END-----Query_OUT Command  in Where Clause-------------------------------------

------------------Get Table Size Details ----------------------

 EXEC sp_msforeachtable 'sp_spaceused ''?''' 
 
-------------------Get Server Info

SELECT  @@servername name ,  substring(@@version  , 1 ,26) version