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

 ---Generate xml format file
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;

--Another sample table and file
create table bcpTest
CASH1 varchar(10),
CASH2 varchar(10),
CASH3 varchar(10),
CASH4 varchar(10)

 -- Generate fmt format file 
 DECLARE @cmd VARCHAR(4000) 
set @cmd ='bcp  myDb.dbo.bcpTest format nul -c -f E:\DATA\myFormatFiletest.fmt -t\"\^\" -r\"\n -T -S'+ @@servername;;
exec master..xp_cmdshell @cmd
 --Modify the format file myFormatFiletest.fmt 
--By adding one row   by copy Row 1 and modify it and change the first column to 1 through 5
--change column count from 4 to 5

1       SQLCHAR             0       10      "\""    0     ""          SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       10      "\"^\""    1     CASH1          SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       10      "\"^\""    2     CASH2          SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       10      "\"^\""    3     CASH3          SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR             0       10      "\"\r\n"   4     CASH4          SQL_Latin1_General_CP1_CI_AS


--Import code

--BULK Insert
FROM 'E:\DATA\user_data.txt'
FORMATFILE ='E:\DATA\myFormatFiletest.fmt' 
select * from bcpTest

--XML format file

 DECLARE @cmd VARCHAR(4000) 
set @cmd ='bcp  myDb.dbo.bcpTest format nul -c -f "E:\DATA\myformatfile.xml" -x -t\^"^^\^" -r \^"\n   -T -S'+ @@servername;;
exec master..xp_cmdshell @cmd

--BULK Insert
FROM 'E:\DATA\user_data.txt' WITH
(FORMATFILE ='E:\DATA\myformatfile.xml' )
select * from bcpTest

---Modified XML file  (add one row and modify it.  (check the -t flag  -t\^"^^\^"  ). The ^ has special meaning so double up to escape it as delimiter.



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

Kimberly Tripp: Building High Performance Stored 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  

$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

—There is a handy function with nested REPLACEs to remove all no printable characters: