bcp a text file with double quotes to a table — another sample

 ---Generate xml format file
DECLARE @cmd VARCHAR(4000) 
set @cmd = 'BCP  [testcs].[dbo].[Table_1] format nul   -f "C:\temp\myformatfile.xml" -x -c -t\^",\^" -r \^"\n  -T -S'+ @@servername
exec master..xp_cmdshell @cmd
  
 --Modify the format file myFormatFile.xml  
--By adding one row in the Record section by copy Row ID=1 and change the ID to 0


----bcp in with all data
exec master..xp_cmdshell  'bcp [testcs].[dbo].[Table_1] IN "C:\temp\mytest.txt" -f "C:\temp\myformatfile.xml" -T  -S'+ @@servername;


 

Optional Parameters in Stored Procedure: how to make it perform well

Kimberly Tripp: Building High Performance Stored Procedures

http://www.sqlskills.com/blogs/kimberly/high-performance-procedures/


Powershell With UPDATE


if (-not(Get-Module -name 'SQLPS')) {
  if (Get-Module -ListAvailable | Where-Object {$_.Name -eq 'SQLPS' }) {
    Push-Location # The SQLPS module load changes location to the Provider, so save the current location
   Import-Module -Name 'SQLPS' -DisableNameChecking  
   }
  }

$val1="abc"
$val2="xyz"
 
$svrname = "MC\MSSQL2014"

Invoke-Sqlcmd -Query "update testcs.dbo.mytest Set col2='$val2' WHERE col1 like '$val1'+'%' " -ServerInstance $svrname  



 

Special Characters in an Int column from Text File

Char Dec Hex Octal HTML Function / Description / Notes

^I 9 0x09 0011 ^I HT horizonal tab [\t]
^J 10 0x0a 0012 ^J LF line feed [\n]
^M 13 0x0d 0015 ^M CR carriage return [\r]
^Z 26 0x1a 0032 ^Z SUB substitute

SELECT Cast(Replace(Replace(Replace(Replace(ANumber,char(9),”),char(10),”) ,char(13),”) ,char(26),”) as INT) ANumber

http://www.idautomation.com/product-support/ascii-chart-char-set.html
—There is a handy function with nested REPLACEs to remove all no printable characters:
http://www.sqlservercentral.com/Forums/Topic860321-338-3.aspx