bcp Unicode Samples




-- -w
-- Performs the bulk copy operation using Unicode characters. This option does not prompt for each field; it uses nchar as the storage type, no prefixes,
-- \t (tab character) as the field separator, and \n (newline character) as the row terminator. -w is not compatible with -c.
 
 
--****************Export Using a stored procedure
 
-------fmt format
DECLARE @cmd0 VARCHAR(4000) 
set @cmd0 = 'BCP  [test1].[dbo].[Tbl_survey] format nul   -f  "C:\temp\Tbl_survey.fmt" -w   -t\t -r \n -T -S'+ @@servername
exec master..xp_cmdshell @cmd0
 
 DECLARE @SqlCmd NVARCHAR(4000) 
 DECLARE @cmd1 NVARCHAR(4000) 
set @cmd1 = 'BCP  "exec test1.dbo.usp_ExportData" queryout  "C:\temp\Tbl_surveynew.txt" -f  "C:\temp\Tbl_survey.fmt" -w  -T  -S'+ @@servername
 
set @SqlCmd = 'exec master..xp_cmdshell ''' + @cmd1 +''''
  
print @SqlCmd
exec sp_executesql @SqlCmd
 
---****************************
 
 
 
--$$$$$$$Export table bcp no header
 
DECLARE @cmd VARCHAR(4000) 
set @cmd = 'BCP  [test1].[dbo].Tbl_survey format nul -f "C:\temp\Tbl_survey.xml" -x -w -t\t  -T -S'+ @@servername
exec master..xp_cmdshell @cmd
 
 
DECLARE @cmd7 NVARCHAR(4000) 
set @cmd7 = 'BCP  [test1].[dbo].[Tbl_survey] OUT  "C:\temp\Tbl_survey2new2.txt" -f  "C:\temp\Tbl_survey.xml" -w -T  -S'+ @@servername
exec master..xp_cmdshell @cmd7
 
----$$$$$$$$$
 
 
 
 
--#########ExportTable with Header  Unicode
DECLARE @sql AS VARCHAR(4000)
DECLARE @cmdAll VARCHAR(4000) 
;with mycte as (select TABLE_CATALOG, table_name, COLUMN_NAME , [ORDINAL_POSITION] from [INFORMATION_SCHEMA].[COLUMNS]
where table_name='Tbl_survey' and TABLE_CATALOG='test1')
   
SELECT @sql='" Select ' + stuff( (SELECT ', '+ Quotename(COLUMN_NAME,'''') +' as '+  Quotename(COLUMN_NAME,']')
FROM mycte m2
WHERE m2.table_name = m1.table_name and m1.TABLE_CATALOG=m2.TABLE_CATALOG
Order by  [ORDINAL_POSITION]
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') 
+  ' UNION ALL Select ' + stuff( (SELECT ', CAST('+ Quotename(COLUMN_NAME,']') +' as varchar(500) ) as '+  Quotename(COLUMN_NAME,']')
FROM mycte m2
WHERE m2.table_name = m1.table_name and m1.TABLE_CATALOG=m2.TABLE_CATALOG
Order by  [ORDINAL_POSITION]
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') 
 + '  FROM ' + 'test1' + '..'+ 'Tbl_survey'  + ' " '
FROM mycte m1
  
set @cmdAll = 'bcp.exe ' +  @sql +  ' queryout "C:\temp\Tbl_survey2new2Header.tx" -f  "C:\temp\Tbl_survey.xml" -T -S'+ @@servername
 
exec xp_cmdShell @cmdAll
 
--#########



-----^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^BULK INSERT ^^^^^^^^^^^^^^^^^^^^^^^^^^^
 BULK INSERT  [test1].[dbo].Tbl_survey
   FROM 'C:\temp\Tbl_survey2new2.txt'
   WITH
      (
         FIELDTERMINATOR ='\t',
         ROWTERMINATOR ='\n',
		 FirstRow=1
		 ,DATAFILETYPE= 'widechar'
      );



	  
-----^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^BULK INSERT ^^^^^^^^^^^^^^^^^^^^^^^^^^^




 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s